Workdays between 2 dates

[1minManager]

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

[Rafael_ANEIC-PY]

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.

[Marc_Dillon · Accepted solution]

We have the WORKDAY() expression.

help.appsheet.com

WORKDAY()

Computed date excluding non-work days

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.

[1minManager]

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

[Marc_Dillon]

I’m not sure either!

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.

[1minManager]

Big shout out here to @Marc_Dillon who definately pointed me in the right direction . 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

[dilawar72]

How is that formula correct? The workdays between those dates would be 8 days if I counted correctly on the calendar. But your result is 2 days. How does that work?

[Segolene_Sansom]

if you read his formula it's actually 10-(15-3-10)=10-2=8. 

so the formula is correct, he just put in the wrong value at the end.

[Martin_Hurtado]

¡Muchas gracias! @1minManager esto es un gran aporte, llevo días con este problema... 

[benny94]

Hello,

i tried with two date: 

21/04/2021

26/05/2021

26 day but this formula return 24

[INN_ENGENHARIA]

NUMBER(TOTALHOURS([END]-[START])/24+1)-

IFS(
YEAR([END]) > YEAR([START]), (52 - WEEKNUM([START]) + WEEKNUM([END])) * 2,
YEAR([END]) = YEAR([START]), (WEEKNUM([END]) - WEEKNUM([START])) * 2
)

[Marc_Dillon]

This is much more easily accomplished by using NETWORKDAYS() in the backend spreadsheet.

[Suvrutt_Gurjar]

This question keeps coming up many times and I agree with @Marc_Dillon  to use spreadsheet function.

In case of work days, there does not appear to be any use case, where one cannot wait for spreadsheet based function to calculate and return the result after a sync.

Out of academic interest, I had also tried to construct an AppSheet functions based NETWORKDAYS() expression and even though it works (Saturdays and Sundays as holidays) , it is unwieldy. Besides spreadsheet's NETWORKDAYS() function is much more flexible with option to choose weekend days etc.

@INN_ENGENHARIA : Out of interest, tried to test your expression , as it is fairly compact. However it is giving different number of work days when compared with G Sheet based NETWORKDAYS(). Also sometimes a year's last week  can have WEEKNUM() as 53 against 52 you have considered. For example, 31st December 2023 will return a week number of 53.

Of course, I may be missing something, please feel free to correct any error in my assumptions.

[INN_ENGENHARIA]

It seams that appsheet always consider a year with 53 weeks, i had to change the expression to 53. 

I'm using a table from smartsheet with dependencies activated, and i can only update the duration and start date, in smartsheet i can not put formulas in this columns.

[Suvrutt_Gurjar]

Thank you for the update. To continue discussion on week numbers, I think a significant number of years will have a 53rd week in any measuring system because a year is strictly a bit more than 52 weeks. A year has either 365 days, that is  52 weeks and 1 day ( non leap) or 366 days that is 52 weeks and 2 days (leap year) 

Please take a look at the ISO WEEK number system article where many years have 53rd week.

ISO week date - Wikipedia

If you are looking for a longish AppSheet based expression ( the following expression considers Saturdays and Sundays as holidays) , please consider the below expression, I tried to come up with. It is  working well for significant number of test cases. Please test well.

(HOUR([Delivery Date]-[Order Date])/24)+ 1 -
IFS(EOWEEK([Delivery Date])=EOWEEK([Order Date]), ( 0
+ IFS(WEEKDAY([Delivery Date])=7,1)

+ IFS(WEEKDAY([Order Date])=1,1)
),

EOWEEK([Delivery Date]-7)=EOWEEK([Order Date]),( 2
+ IFS(WEEKDAY([Delivery Date])=7,1)

+ IFS(WEEKDAY([Order Date])=1,1)
),

EOWEEK([Delivery Date]-7)>EOWEEK([Order Date]), (HOUR(EOWEEK([Delivery Date]-7)-
EOWEEK([Order Date]))/84)+2 +
IFS(WEEKDAY([Delivery Date])=7,1)+
IFS(WEEKDAY([Order Date])=1,1)

)

The test results are as follows -[WorkdaysG] is based on NETWORKDAYS() spreadsheet function in G sheet. [Workdays V2] is the expression I shared above and [Workdays_INN_ENGENHARIA] is based on the expression you shared with 53 weeks.

[Delivery Date] is equivalent to [End Date] and [Order Date] is equivalent to [Start Date]

Hope this helps.

[INN_ENGENHARIA]

"I've never seen someone put so much effort into finding an answer. Best regards.