Calculating how late an employee came

[Rabbi_Sebbag]

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?

Thanks!

[Kamille_Parks]

“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.

[Rabbi_Sebbag]

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?

[Kamille_Parks]

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:

IF({
   Start Date}, 
   SWITCH(
      WEEKDAY({Start Date}), 
      0, 7, 
      5, 3, 
      8
   ) * 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.

[Rabbi_Sebbag]

Kamille_Parks:

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

Thank you for all your help.

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

[Kamille_Parks]

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.