locked
Formula that excludes weekends for vacation RRS feed

  • 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

Answers

  • 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.

    SharePoint Server 2019 has been released, you can click here to download it.
    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 Dennis Guo Thursday, August 29, 2019 8:46 AM
    • Marked as answer by Silvestre Kassoka 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.

    SharePoint Server 2019 has been released, you can click here to download it.
    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 Dennis Guo Thursday, August 29, 2019 8:46 AM
    • Marked as answer by Silvestre Kassoka 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