Skip to main content

Number of working days between two dates (without weekends)

  • November 22, 2017
  • 12 replies
  • 345 views
A
Forum|alt.badge.img

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

J
Forum|alt.badge.img+4

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.

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • November 23, 2017

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

B
M
M
A
Forum|alt.badge.img
  • Adnan_Halilovic
  • Author
  • New Participant
  • November 23, 2017

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

A
W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • November 23, 2017

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:

A
J
Forum|alt.badge.img+4

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!!

A
A
Forum|alt.badge.img
  • Adnan_Halilovic
  • Author
  • New Participant
  • December 25, 2017

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!

P
  • Patricia_Li
  • Participating Frequently
  • January 2, 2018

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.

M
Forum|alt.badge.img+2

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.

Q
U
N
N
Forum|alt.badge.img+4
  • nnnnneil
  • Known Participant
  • August 14, 2019

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:

U
N
B
Forum|alt.badge.img
  • bjenkins
  • Participating Frequently
  • March 19, 2020

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


That’s an awesome solution. Thanks very much!

Q

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)

P
NLOIA
J
  • Jorge_Chavez
  • New Participant
  • July 20, 2021

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.

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?

Terms & ConditionsAccessibility statement