Custom Threshold Formula That Uses Either the Actualdate or Refdate

To calculate daily totals using either the earned day (ActualDate) or overtime day (RefDate) from the person's work day definition, create a custom threshold formula.

Here are detailed results for the sample November 11 through 17, 2019 time card. The person reported time for Monday through Wednesday, November 11 through 13.

Day	Payroll Time Type	Start Time	Stop Time
Mon to Tue Nov 11 to 12	Regular	5:00a 1:00p 6:00p	7:00a 5:00p 3:00a
Wed, Nov 13	Regular	5:00a	7:00a

Day

Payroll Time Type

Start Time

Stop Time

Mon to Tue

Nov 11 to 12

Regular

5:00a

1:00p

6:00p

7:00a

5:00p

3:00a

Wed, Nov 13

Regular

5:00a

7:00a

The daily total is calculated using either the ActualDate or RefDate instead of the start time. Both dates still use the overtime work day. Time worked above the 8-hour daily ActualDate or RefDate threshold is overtime. The explanations for ActualDate and RefDate both use November 11, because both daily totals have ActualDates and RefDates for November 11. Only RefDate has entries for November 10.

Daily Totals by ActualDate: The custom threshold formula determines that the person worked 17 hours on November 11 because all of the first three entries start on that day. The ActualDate for the first time entry on 2019-Nov-12 is 2019-Nov-11 because there's less than 180 minutes between when it starts and the 2019-Nov-12 03:00 entry stops.

Start Time	Stop Time	Measure	Payroll Time Type	ActualDate	RefDate
2019-Nov-11 05:00	2019-Nov-11 07:00	2h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 13:00	2019-Nov-11 17:00	4h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 18:00	2019-Nov-12 03:00	9h	Regular	2019-Nov-11	2019-Nov-11
2019-Nov-12 05:00	2019-Nov-12 07:00	2h	Regular	2019-Nov-11	2019-Nov-11
	Daily total:	17h

2019-Nov-12 13:00	2019-Nov-12 17:00	4h	Regular	2019-Nov-12	2019-Nov-11
2019-Nov-12 18:00	2019-Nov-13 03:00	9h	Regular	2019-Nov-12	2019-Nov-12
2019-Nov-13 05:00	2019-Nov-13 07:00	2h	Regular	2019-Nov-12	2019-Nov-12
Daily total:		15h

Daily Totals by RefDate. The custom threshold formula determines that the person worked 15 hours on November 11 because all three entries are within the work day defined as 5:00p to 5:00p.

Start Time	Stop Time	Measure	Payroll Time Type	ActualDate	RefDate
2019-Nov-11 05:00	2019-Nov-11 07:00	2h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 13:00	2019-Nov-11 17:00	4h	Regular	2019-Nov-11	2019-Nov-10
Daily total:		6h

2019-Nov-11 18:00	2019-Nov-12 03:00	9h	Regular	2019-Nov-11	2019-Nov-11
2019-Nov-12 05:00	2019-Nov-12 07:00	2h	Regular	2019-Nov-11	2019-Nov-11
2019-Nov-12 13:00	2019-Nov-12 17:00	4h	Regular	2019-Nov-12	2019-Nov-11
Daily total:		15h

2019-Nov-12 18:00	2019-Nov-13 03:00	9h	Regular	2019-Nov-12	2019-Nov-12
2019-Nov-13 05:00	2019-Nov-13 07:00	2h	Regular	2019-Nov-12	2019-Nov-12
Daily total:		11h

Calculated Overtime by ActualDate: Because the threshold is 8 hours, the formula splits the 2019-Nov-11 18:00 to 2019-Nov-12 03:00 entry into these two entries:

The regular 2019-Nov-11 18:00 to 2019-Nov-11 20:00 entry
The overtime 2019-Nov-11 20:00 -- 2019-Nov-12 03:00 entry

But the overtime entry spans working days. So the formula splits it into 2 overtime entries: 2019-Nov-11 20:00 to 2019-Nov-12 00:00 and 2019-Nov-12 00:00 to 2019-Nov-12 03:00. Because the 2019-Nov-12 05:00 entry starts less than 180 minutes after the end of the previous entry, it too is included in the overtime calculation for November 11.

Start Time	Stop Time	Measure	Payroll Time Type	ActualDate	RefDate
2019-Nov-11 05:00	2019-Nov-11 07:00	2h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 13:00	2019-Nov-11 17:00	4h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 18:00	2019-Nov-11 20:00	2h	Regular	2019-Nov-11	2019-Nov-11
2019-Nov-11 20:00	2019-Nov-12 03:00	7h	Overtime	2019-Nov-11	2019-Nov-11
2019-Nov-12 05:00	2019-Nov-12 07:00	2h	Overtime	2019-Nov-11	2019-Nov-11

2019-Nov-12 13:00	2019-Nov-12 17:00	4h	Regular	2019-Nov-12	2019-Nov-11
2019-Nov-11 18:00	2019-Nov-11 20:00	2h	Regular	2019-Nov-12	2019-Nov-12
2019-Nov-11 20:00	2019-Nov-11 22 00	2h	Regular	2019-Nov-12	2019-Nov-12
2019-Nov-11 22:00	2019-Nov-12 03:00	5h	Overtime	2019-Nov-12	2019-Nov-12
2019-Nov-13 05:00	2019-Nov-13 07:00	2h	Overtime	2019-Nov-12	2019-Nov-12

Calculated Overtime by RefDate: Because the threshold is 8 hours, the formula splits the 2019-Nov-11 18:00 to 2019-Nov-12 03:00 entry into these two entries:

The regular 2019-Nov-11 18:00 to 2019-Nov-12 02:00 entry
The overtime 2019-Nov-12 02:00 to 2019-Nov-12 03:00 entry

The remaining entries starting on November 12 all stop before day's end at 5:00p, so they're calculated as overtime.

Start Time	Stop Time	Measure	Payroll Time Type	ActualDate	RefDate
2019-Nov-11 05:00	2019-Nov-11 07:00	2h	Regular	2019-Nov-11	2019-Nov-10
2019-Nov-11 13:00	2019-Nov-11 17:00	4h	Regular	2019-Nov-11	2019-Nov-10

2019-Nov-11 18:00	2019-Nov-12 02:00	8h	Regular	2019-Nov-11	2019-Nov-11
2019-Nov-12 02:00	2019-Nov-12 03:00	1h	Overtime	2019-Nov-11	2019-Nov-11
2019-Nov-12 05:00	2019-Nov-12 07:00	2h	Overtime	2019-Nov-11	2019-Nov-11
2019-Nov-12 13:00	2019-Nov-12 17:00	4h	Overtime	2019-Nov-12	2019-Nov-11

2019-Nov-11 18:00	2019-Nov-11 02:00	8h	Regular	2019-Nov-12	2019-Nov-12
2019-Nov-12 02:00	2019-Nov-12 03:00	1h	Overtime	2019-Nov-12	2019-Nov-12
2019-Nov-13 05:00	2019-Nov-13 07:00	2h	Overtime	2019-Nov-12	2019-Nov-12

Here's the pseudo code that you can use to create threshold formulas that calculate overtime using the earned day as the ActualDate.


….. . 

INPUTS ARE 
  HWM_CTXARY_RECORD_POSITIONS,
  HWM_CTXARY_HWM_MEASURE_DAY,
  Measure,  
  StartTime,
  StopTime,
  ActualDate


….

wkTotalHrsDay = 0 
lastActualDate= nulldate
nidx = 0
WHILE (nidx < wMaAry ) LOOP (	  
   nidx = nidx + 1	 
   tcMeasure = 0  
   tcMeasureDay = 0
   tcActualDate = nullDate
	
	aiRecPosition   = HWM_CTXARY_RECORD_POSITIONS[nidx] 
	if (MEASURE.exists(nidx) ) then ( tcMeasure  = MEASURE[nidx]   )  
	if (ActualDate.exists(nidx) ) then (tcActualDate = StartTime [nidx]   )  

if (lastActualDate <> tcActualDate) then  ( 
   / lastActualDate =  tcActualDate
   wkTotalHrsDay = tcMeasure  
) else (   
   wkTotalHrsDay =  wkTotalHrsDay  + tcMeasure  
)

If  (wkTotalHrsDay	> p threshold (
   /* create overtime …  */
)
…

The logic to use the overtime day as the RefDate is the same. Just replace all instances of ActualDate in the preceding pseudo code with RefDate.