Number of working days between two dates (without weekends)
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
Page 1 / 1
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.’)
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.’)
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!!
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!
Cheers, and happy holidays!
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.
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.
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
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.’)
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 like this! (like McKenna_Johnson)
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.
I have used this formula with the following dates however, the outcome was not as expected.
