question

RJF-4684 avatar image
0 Votes"
RJF-4684 asked RJF-4684 commented

Calculate Workdays Between 2 Dates Excluding Holidays

Hello,

I have a calculation that will generate the number of workdays between two dates.

=IF(ISERROR(DATEDIF(Created,[Shutdown Start],"d")),"",(DATEDIF(Created,[Shutdown Start],"d"))-INT(DATEDIF(Created,[Shutdown Start],"d")/7)*2-IF((WEEKDAY([Shutdown Start])-WEEKDAY(Created))<0,2,0)-IF(OR(AND(WEEKDAY([Shutdown Start])=7,WEEKDAY(Created)=7),AND(WEEKDAY([Shutdown Start])=1,WEEKDAY(Created)=1)),1,0)-IF(AND(WEEKDAY(Created)=1,(WEEKDAY([Shutdown Start])-WEEKDAY(Created))>0),1,0)-IF(AND(NOT(WEEKDAY(Created)=7),WEEKDAY([Shutdown Start])=7),1,0))

Is there a way to also exclude the Holidays? I was wondering if a separate list of Holiday Dates would help.

Thanks

office-sharepoint-server-development
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, @RJF-4684 ,

Does the reply solve you question? Any further question about it?

0 Votes 0 ·

Hello @JerryXu-MSFT

The reply does answer my question but does not resolve the issue.

Thanks

0 Votes 0 ·

1 Answer

JerryXu-MSFT avatar image
0 Votes"
JerryXu-MSFT answered

Hi, @RJF-4684 ,

AFAIK, you will not be able to make a calculate column to do this. It cannot read data from another list. And there is no function available to identify whether one day is a holiday.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.