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
Did you mean:  4 - Data Explorer

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  16 - Uranus

“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.  4 - Data Explorer

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?  16 - Uranus

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.  4 - Data Explorer Thank you for all your help.

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

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. 