Time Off Hours Request Duration Formula

[egordin]

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:

1. Work hours are 8am to 5pm
2. Exclude 12pm-1pm if the span crosses that time period, since there should only be 8 hours between 8am and 5pm
3. Exclude weekends
4. Ideally, and I realize this is a tall order, it would exclude New Year’s Day, Independence Day, Memorial Day, Labor Day, Thanksgiving Day, and Christmas Day for say, the past 5 years and next 5 years.

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?

[Pascal_Gallais- · Accepted solution]

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

[egordin]

@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?

[Pascal_Gallais- · Accepted solution]

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

[egordin]

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}

I also used this Workday Holiday base to generate a list of the holidays that I need, using a lookup field from a linked field that would apply to all records using an automation. 

[egordin]

@Pascal_Gallais- Actually, this solution doesn't seem to work for the following cases

1. Where time off requests start and end on the same day but are only in the morning (less than 8 hours)
2. Where requests starts or ends are not "on the hour" but less. We would love to limit our team to requests that start only on the half hour or hour, but there's no good way to do that right now in Airtable forms (that I know of). Therefore, some requests are from say, 9:30am to 2:00 pm.

What's the best way to address that?

[Pascal_Gallais-]

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

IF(DATETIME_FORMAT(Start,'YYYYMMDD') = DATETIME_FORMAT(End,'YYYYMMDD'),

0,

(IF(HOUR(Start)<=12, 16 - (HOUR(Start)),

17 - (HOUR(Start)))) * {Start Day}

)

-2- Add a formula "Single day Hours" to get the number of hours when start and end are on the same date

IF(DATETIME_FORMAT(Start,'YYYYMMDD') = DATETIME_FORMAT(End,'YYYYMMDD'),

IF(HOUR(End)<=12,(HOUR(End)-HOUR(Start))*{Start Day},

(HOUR(End)-HOUR(Start)-1)*{Start Day}),0)

-3- Modify formula "Total Hours" to add "Single day Hours"

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

[egordin]

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! 

[Pascal_Gallais-]

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:

IF(DATETIME_FORMAT(Start,'YYYYMMDD') = DATETIME_FORMAT(End,'YYYYMMDD'),

IF(OR(HOUR(End)<=12, HOUR(Start)>=12), (HOUR(End)-HOUR(Start))*{Start Day},

(HOUR(End)-HOUR(Start)-1)*{Start Day}),0)

Now here is the final result when including minutes:

I added 4 formulas:

Formula "Start Minutes":

IF(OR(MINUTE(Start) = 0, HOUR(Start) = 12), 0, 60 - MINUTE(Start))

Formula "End Minutes"

IF(HOUR(End) = 12, 0 ,MINUTE(End))

Formula Minutes

{Start Minutes}+{End Minutes}

Formula Hours Adjustment

IF(AND({Start Minutes}=0,Minutes<=59,HOUR(Start)!=12),0,

IF(AND({Start Minutes}=0,Minutes<=59,HOUR(Start)=12),-1,

IF(AND({Start Minutes}=0,Minutes>59,HOUR(Start)=12),0,

IF(AND({Start Minutes}=0,Minutes>59,HOUR(Start)!=12),1,

IF(AND({Start Minutes}>0,Minutes<=59,HOUR(Start)!=12),-1,

IF(AND({Start Minutes}>0,Minutes<=59,HOUR(Start)=12),-2,

IF(AND({Start Minutes}>0,Minutes>59,HOUR(Start)=12),-1,

IF(AND({Start Minutes}>0,Minutes>59,HOUR(Start)!=12),0

))))))))

And finaly, when include the result of "Hours Adjustment" into "Total Hours":

{Complete Hours}+{Start day Hours}+{End day Hours}+{Single day Hours}+{Hours adjustment}

And we define the formula "Total Minutes":

IF(Minutes>59,Minutes-60,Minutes)

Be aware that I have done this quickly and did not spend time on testing every options.

Regards,

Pascal