Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Calculating how late an employee came

Topic Labels: Formulas
327 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

5 Replies 5

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

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.

staff attendance tracker

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.