Help

Re: Number of working days between two dates (without weekends)

6497 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Adnan_Halilovic
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello,

I’m trying to solve the following problem:
I have created a table which has 2 columns {DATE FROM} and {DATE TO}}. The third one I have is called {WORKING DAYS}, so that one should show a number of working days between selected dates (from and to).

The problem I’m having is that I cannot calculate number of working days between them (without weekends), I can just get all days, which is not useful for me in this case…

Is there any chance to solve it?

WORKDAY() function doesn’t help, since it just creates a due date when I enter parameters.

I could solve my issue, if there are for loop, for each, or any other loop functions in the functions menu.

Thank you

12 Replies 12

The best answer here would be the flip side of the Workday() function - how about it Airtable??

Otherwise it may be possible to build a horrid nested if formula- or you could consider the option of having Zapier calculate the value - you could use a code step in a zap to do this. The downside is that Zaps aren’t instant yet (Airtable!!) and only run once per record.

Caveat: I just tossed this together, and while it works for the handful of test dates I’ve thrown at it, I haven’t tested it thoroughly.

I think this formula will get you what you want. (Note: It may need to be adjusted to support regional interpretations of ‘day-of-week’ and ‘weekend.’)

DATETIME_DIFF({Date to},{Date from},'days')-
(INT(DATETIME_DIFF({Date to},{Date from},'days')/7)+
IF(DATETIME_FORMAT({Date from},'E')>DATETIME_FORMAT({Date to},'E'),1,0))*2

Hi @W_Vann_Hall, looks like it works for me! :slightly_smiling_face:
Thanks a lot! Cheers

That’s great! As I was doing errands this evening, I kept running through different test cases in my head to see what I’d managed to overlook. Maybe this time the blind pig found an acorn… :winking_face:

There’s now announcement yet but I spotted on the What’s New page that a new function is available Workday_Diff() is now available - it allows for the exclusion of non work dates as well!

I’ve tried it in my HR app (and will publish an update to the Universe soon) and it works well!!

Nice early Christmas present!!

That’s awesome!
Thanks a lot for the info! :slightly_smiling_face:
Cheers, and happy holidays!

Patricia_Li
5 - Automation Enthusiast
5 - Automation Enthusiast

Looks like I got beat to the punch, but we’ve added a WORKDAY_DIFF formula! Documentation here.

It takes in a start date, end date, and optional list of holidays, like so: WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10'), and returns the number of working days between the start date and end date, inclusive.

For those of you familiar with Excel or Sheets, it’s pretty much isomorphic to the NETWORKDAYS formula in those products.

McKenna_Johnson
5 - Automation Enthusiast
5 - Automation Enthusiast

I am having trouble understanding the WORKDAY_DIFF and DATETIME_DIFF. Primarily I need it to return hours and minutes within 5 working days. Not just single days with the WORKDAY_DIFF and not just total hours and minutes including weekends with DATETIME_DIFF.

Example: Start a project at 4pm Friday and finish 9am Monday, I would like my Turn Around column to say 2 hours.

I have tried Caveat’s formula and my own versions of it but it will only return 1 or 3 days.

Also looking for a solution to @McKenna_Johnson’s request. Basically a WORKTIME_DIFF :slightly_smiling_face:

That’s an awesome solution. Thanks very much!

Also looking for a solution like this! (like McKenna_Johnson)

I have used this formula with the following dates however, the outcome was not as expected.

WORKDAY_DIFF({Date Received}, {Date Completed})*24
Date Received = 16/7/2021 10:23am
Date Complete = 20/7/2021 10:23am

The result should had been 48 but instead I got 72. Can you please advise why?