Formula that excludes weekends for vacation

• Question

• Good day

I have a formula that calculates dias taken for vacation that exculdes weekend. However i noticed when the days selected is

a Friday the calculation does not work. Herewith the formula.

=IF(AND((WEEKDAY(termo;2))<(WEEKDAY(Início;2));((WEEKDAY(Início;2))-(WEEKDAY(termo;2)))>1);(((DATEDIF(Início;termo;"D")+1))-(FLOOR((DATEDIF(Início;termo;"D")+1)/7;1)*2)-2);(((DATEDIF(Início;termo;"D")+1))-(FLOOR((DATEDIF(Início;termo;"D")+1)/7;1)*2)))

Thank you.

Kassoka

Wednesday, August 21, 2019 8:46 AM

• Hi,

Try this.

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

Best Regards,

Lee

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

• Proposed as answer by Thursday, August 29, 2019 8:46 AM
• Marked as answer by Thursday, August 29, 2019 9:15 AM
Thursday, August 22, 2019 3:18 AM

All replies

• Hi,

Try this.

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

Best Regards,

Lee

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

• Proposed as answer by Thursday, August 29, 2019 8:46 AM
• Marked as answer by Thursday, August 29, 2019 9:15 AM
Thursday, August 22, 2019 3:18 AM
• Hi Lee,

Thanks a lot your formula works i tried it in my test environment its working fine.

Regards

Kassoka

Thursday, August 29, 2019 9:16 AM