Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 09, 2021 09:10 AM
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!
Feb 09, 2021 09:22 AM
“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.
Feb 09, 2021 09:28 AM
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?
Feb 09, 2021 10:48 AM
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.
Feb 09, 2021 01:45 PM
Thank you for all your help.
The formulas are working but not giving me the right calculation. What am I doing wrong?
Feb 09, 2021 01:49 PM
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.