Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Time Off Hours Request Duration Formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

2
788
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

- Work hours are 8am to 5pm
- Exclude 12pm-1pm if the span crosses that time period, since there should only be 8 hours between 8am and 5pm
- Exclude weekends
- 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?

Solved! Go to Solution.

2 Solutions

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

8 Replies 8

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 23, 2024 11:05 AM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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}`

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Where time off requests start and end on the same day but are only in the morning (less than 8 hours)
- 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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

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