Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 16, 2024 11:04 AM - edited Aug 16, 2024 11:31 AM
I have been trying to develop a formula to output the amount of work hours between two date/time fields. This is for a base that has Time Off requests for employees.
I have a field with a start date like this (9/16/2024 10:00am) and a field with an end date like this (9/28/2024 3:00pm).
What is a formula that would calculate the amount of work hours between those two fields, assuming:
This has been driving me crazy for a long time, and I cannot come up with a reliable solution, even with ChatGPT. Can anyone help?
Solved! Go to Solution.
Aug 18, 2024 02:18 AM
Hello,
You could do this using the WORDAY_DIFF formula. It is laborious to setup due to the list of days off to exlude, but it does the job;
I assumed that all days, except Start and End, are full time (8 hours).
You also need to make sure that your Start and End fields are set on a specified date format with the same time zone for all collaborators.
Complete days is a formula that returns the number of working days, excluding start and end day:
WORDAY_DIFF function takes 3 arguments: start date, end date, and the list of dates to exclude in ISO format; this is the laborious part since you want to exlude 60 dates.
Complete hours is a formula that multiplies the number of complete days by 8 :
Note that if I am wrong in assuming that start date or end date may not be complete (less than 8 hours), you can stop there and just remove the "-2" in "complete days" formula.
Start Day is a copy of "complete days" formula replacing "end" date by "start" date and without "-2":
It returns 0 if start date is a day off, 1 if not.
Start day hours returns the number of working hours for start date:
End Day is a copy of "Start Day" formula for "End" date:
End day hours retruns the number of working hours for End Date:
Total Hours sums the 3 intermediate results:
Regards,
Pascal
Aug 24, 2024 07:23 AM
Hello,
This is because this formula takes off 2 days from the result to exclude start en end days that may not be complete. Hence, you end up with a negative result when start and end are on the same date.
You should modify the formula as follows:
You also need to modify formula "End day" to set the result to 0 when Start and End dates are on the same day:
Regards,
Pascal
Aug 18, 2024 02:18 AM
Hello,
You could do this using the WORDAY_DIFF formula. It is laborious to setup due to the list of days off to exlude, but it does the job;
I assumed that all days, except Start and End, are full time (8 hours).
You also need to make sure that your Start and End fields are set on a specified date format with the same time zone for all collaborators.
Complete days is a formula that returns the number of working days, excluding start and end day:
WORDAY_DIFF function takes 3 arguments: start date, end date, and the list of dates to exclude in ISO format; this is the laborious part since you want to exlude 60 dates.
Complete hours is a formula that multiplies the number of complete days by 8 :
Note that if I am wrong in assuming that start date or end date may not be complete (less than 8 hours), you can stop there and just remove the "-2" in "complete days" formula.
Start Day is a copy of "complete days" formula replacing "end" date by "start" date and without "-2":
It returns 0 if start date is a day off, 1 if not.
Start day hours returns the number of working hours for start date:
End Day is a copy of "Start Day" formula for "End" date:
End day hours retruns the number of working hours for End Date:
Total Hours sums the 3 intermediate results:
Regards,
Pascal
Aug 23, 2024 11:05 AM
@Pascal_Gallais- thank you so much for the detailed and thoughtful response. I tried implementing it and I can't seem to get it to work correctly. Below is what I have, and please note that I did not exclude holidays yet in my formula, just for simplicity for now. One issue seems to be with the Complete Days formula since it results in a negative number for same day requests. What am I doing wrong?
Aug 24, 2024 07:23 AM
Hello,
This is because this formula takes off 2 days from the result to exclude start en end days that may not be complete. Hence, you end up with a negative result when start and end are on the same date.
You should modify the formula as follows:
You also need to modify formula "End day" to set the result to 0 when Start and End dates are on the same day:
Regards,
Pascal
Aug 25, 2024 05:02 PM
This works! Thank you so much! I modified the start date formula to be <=12 rather than just <12 so that time off requests that started around noon didn't count the lunch hour.
(IF(HOUR({Start Date})<=12,16-(HOUR({Start Date})),17-(HOUR({Start Date}))))*{Start Day}
Aug 27, 2024 12:03 PM - edited Aug 27, 2024 12:04 PM
@Pascal_Gallais- Actually, this solution doesn't seem to work for the following cases
What's the best way to address that?
Aug 28, 2024 01:17 AM
Hello,
This is because I assumed that start day always ends at 5pm.
Here is what I suggest for your first point.
-1- Modify formula "Start day Hours" in order to set the result to 0 if start day and end day are on the same date
For your second point, that means that we need to compute the minutes and review the hours computation. Before doing so, we need to answer the following questions.
If start day hour is 12:30, do we consider that it is 1pm, or do we count these 30 minutes?
If end day hour is 12:30, do we consider that it is 12:00, or do we count these 30 minutes?
Regards,
Pascal
Aug 28, 2024 10:26 AM
Hi @Pascal_Gallais- ! Thank you for the quick response.
For your questions, no time between 12pm and 1pm should be counted, so if the time off request lists 12:30pm as the start time, then the actual time off would be counted from 1pm. Likewise if the end day hour is 12:30pm, then we should consider that 12pm. Thanks again!
Aug 29, 2024 10:42 AM
Hello,
I had a look at the second point (minutes). By doing so, I realized that my formula to compute the hours for a single day was not complete since the case of a request starting after 12 was not taken into account.
Hence formula for "Single day hours" field should be:
I added 4 formulas:
Formula "Start Minutes":