how to calculate number of days between 2 dates

Andy 1,161 Reputation points
2022-12-28T06:40:25.047+00:00

I'm trying to calculate the number of working days between 2 dates [Start Date] and [End Date], given 2 dates. means that I want to exclude the weekends, just count the working days(Monday,...Friday). How to do that?

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
1,470 questions
SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,365 questions
No comments
{count} votes

Accepted answer
  1. Xuyan Ding - MSFT 6,016 Reputation points Microsoft Employee
    2022-12-28T09:12:39.843+00:00

    Hi @Andy ,

    Try this formula:

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

    274489-image.png
    274525-image.png


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.


0 additional answers

Sort by: Most helpful