Forum Discussion

Koong's avatar
Koong
Copper Contributor
Nov 13, 2023
Solved

How to calculate the duration in days excluding weekends between 2 date by using formula

Hi,

 

Please help me how to calculate the duration day excluding weekend,

I try to using formula like as bellow, but it's not working:

"=IF(AND(WEEKDAY([End Date])>1, WEEKDAY([End Date])<7), IF(AND(WEEKDAY([Start Date])>1, WEEKDAY([Start Date])<7), DATEDIF([Start Date], [End Date], "D") - (INT(DATEDIF([Start Date], [End Date], "D") / 7) * 2), DATEDIF([Start Date], [End Date], "D") - (INT(DATEDIF([Start Date], [End Date], "D") / 7) * 2) - 1), DATEDIF([Start Date], [End Date], "D") - (INT(DATEDIF([Start Date], [End Date], "D") / 7) * 2) - 2)"

 



Thanks,

 

 

sharepoint
  • Koong's avatar
    Koong
    Nov 13, 2023

    Hi,
    i trying add plus 1, formula working well.

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

     

     

1 Reply

  • Koong's avatar
    Koong
    Copper Contributor
    Nov 13, 2023

    Hi,
    i trying add plus 1, formula working well.

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

     

     

Resources