Calculating how late an employee came

Is there a way to use the datetime_diff formula (or maybe something else) to calculate how much time an employee missed by coming late, leaving early, or not coming in at all?


“Late” and “early” imply there is a known amount of time the employee was supposed to be there. So, assuming you have a Duration field that gives {Scheduled Shift Length}:

IF(AND({Start Date}, {End Date}), MAX(DATETIME_DIFF({End Date}, {Start Date}, 'seconds'), 0), 0) - {Scheduled Shift Length}

^ This will give you an output that you can format as a Duration to show how much time under or over an employee worked that shift.

All employees start at 9 (Sun - Fri) and end at 5 (Mon - Thur) Friday is until 12 and Sunday is until 4. Is there a way to calculate based on that?

Of course.

Based on what you said, Sunday shifts are 7 hours, Friday shifts are 3 hours, and all other shifts are 8 hours. Therefore, your {Scheduled Shift Length} field would be a formula that looked like this:

   Start Date}, 
      WEEKDAY({Start Date}), 
      0, 7, 
      5, 3, 
   ) * 3600

^ In plain English, that formula reads: “Unless the {Start Date} field is blank, check if the {Start Date} is on a Sunday (0) and if it is return the number 7, otherwise check if the {Start Date} is a Friday (5) and if it is return the number 3, otherwise return the number 8. Then multiply the returned number * 3600”

The number of hours is multiplied by 3600 to get the amount of time in seconds, which will let you format the field as a duration.

1 Like

Thank you for all your help.

The formulas are working but not giving me the right calculation. What am I doing wrong?

Are you certain you related {Start Date} to {Time Arrived} and {End Date} to {Time Left} appropriately? Notice the order in which Start/End appear in the formula I gave.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.