Workdays between 2 dates

Just wondering if anyone has a formula for calculating the number of Workdays beween 2 dates? Including both those dates. If its relevant, the Start and End dates may be a weekend :confused:

Hello @1minManager, i can’t think of a way to do that without having an auxiliary table with all possible days listed, since we can’t auto generate a range of dates in a list format.

1 Like

We have the WORKDAY() expression.

For convenience, assume another column “total days” which is equal to:
TOTALHOURS( [end] - [start] ) / 24

Then get total work days with:

[total days]
-
(
  TOTALHOURS( WORKDAY( [start] , [total days] ) - [start] ) / 24
  -
  [total days]
)

Please test well.

2 Likes

HI @Marc_Dillon I’m not sure thats what I want. Its more a formula where:

[Start Date] = “21/4/2021”
[End Date] = “28/8/2021”
And the result would be 6 work days (inclucing the start and end day)

I’ve got part of it done. Such that it counts the total days, does (LOWER(…)/7)*5 to get total full weeks and then 5 days per week. Its just how to count the other bit without resorting to a massive IFS() forumla :confused:

1 Like

I’m not sure either! :wink:

I CAN say that the logic behind my expression made sense to me yesterday, and it still does today. I also have no change in my understanding of your requirements from your first post to your last.

Basically my expression does this:

  1. Calculate total days between 2 dates.
  2. Calculate a WORKDAY, based on the start date, and the total days from 1.
  3. Compare the result of 2, with the actual end date, finding the difference in days.
  4. Subtract the difference from 3, from the total days from 1.
3 Likes

Big shout out here to @Marc_Dillon who definately pointed me in the right direction :grin:. So here we have it, a formula to calculate the number of working days between 2 dates including those days:

ROUND((TOTALHOURS([To Date]-[From Date])/24)+1)-
(
ROUND((TOTALHOURS(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))-[From Date])/24)+1)-
IFS(
WEEKNUM([To Date])=WEEKNUM(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))),1,
WEEKNUM([To Date])<WEEKNUM(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))),3
)-
ROUND((TOTALHOURS([To Date]-[From Date])/24)+1)
)

Some quick notes for people trying to follow this:

  • The +1 is there to include the start date in the calculation
  • ROUND() is required since WORKDAY() will only work with numbers and the /24 bit actually turns it into a decimal
  • The IFS() section deducts 1 off the total if after calcuating the WORKDAY() date this is in the same week as [To Date]. If its in a later week it deducts 3 days. This is because sometimes when you do the WORKDAY() it is pushed into the following week

A summary of each section is as follows. Dates used is 21/4/2021 to 30/4/2021:
ROUND((TOTALHOURS([To Date]-[From Date])/24)+1)
Find total days including weekend = 10

ROUND((TOTALHOURS(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))-[From Date])/24)+1)
Find total days for WORKDAY([From Date],10) = 15

IFS(
WEEKNUM([To Date])=WEEKNUM(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))),1,
WEEKNUM([To Date])<WEEKNUM(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))),3
)
IF WEEKNUM([To Date]) equals WEEKNUM(WORKDAY([From Date],10)) then deduct of 1 otherwise deduct off 3. This could probably be an IF() to be honest… = 3

So the result is:
10-(15-3-10) = 2

This is slightly neater:
ROUND((TOTALHOURS([To Date]-[From Date])/24)+1)-
(
ROUND((TOTALHOURS(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))-[From Date])/24)+1)-
IF(WEEKNUM([To Date])=WEEKNUM(WORKDAY([From Date],ROUND((TOTALHOURS([To Date]-[From Date])/24)+1))),1,3)-
ROUND((TOTALHOURS([To Date]-[From Date])/24)+1)
)

Posted in case this helps someone out

Simon@1minManager.com

1 Like