Forum Discussion

Rickytruco's avatar
Rickytruco
Brass Contributor
Mar 25, 2024
Solved

Sharepoint list calculated column difference between dates (no weekends)

=DATEDIF([Fecha de inicio], IF(ISBLANK([Fecha cierre]), TODAY(), [Fecha cierre]), "D") + 1 - (INT(DATEDIF([Fecha de inicio], IF(ISBLANK([Fecha cierre]), TODAY(), [Fecha cierre]), "D") / 7) * 2) - IF...
Lists
microsoft lists
SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Mar 25, 2024

    Rickytruco the formula I use for calculating working days is as follows:

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

     

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

Rob_Elliott's avatar
Rob_Elliott
Bronze Contributor
Mar 25, 2024

Rickytruco the formula I use for calculating working days is as follows:

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

 

 

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

  • Rickytruco's avatar
    Rickytruco
    Brass Contributor
    Mar 25, 2024

    Hey! Ty for your quick reply. This was just missing the part where End Date could be blank, so then considered the "End" date as Today. I added it with ChatGPT and is running as expected 🙂 Thank you lots! 

    =IF(ISBLANK([Fecha cierre]), 
    DATEDIF([Fecha de inicio], TODAY(), "d") + 1 - 
    INT(DATEDIF([Fecha de inicio], TODAY(), "d") / 7) * 2 - 
    IF((WEEKDAY(TODAY()) - WEEKDAY([Fecha de inicio])) < 0, 2, 0) - 
    IF(OR(AND(WEEKDAY(TODAY()) = 7, WEEKDAY([Fecha de inicio]) = 7), AND(WEEKDAY(TODAY()) = 1, WEEKDAY([Fecha de inicio]) = 1)), 1, 0) - 
    IF(AND(WEEKDAY([Fecha de inicio]) = 1, (WEEKDAY(TODAY()) - WEEKDAY([Fecha de inicio])) > 0), 1, 0) - 
    IF(AND(NOT(WEEKDAY([Fecha de inicio]) = 7), WEEKDAY(TODAY()) = 7), 1, 0), 

    DATEDIF([Fecha de inicio], [Fecha cierre], "d") + 1 - 
    INT(DATEDIF([Fecha de inicio], [Fecha cierre], "d") / 7) * 2 - 
    IF((WEEKDAY([Fecha cierre]) - WEEKDAY([Fecha de inicio])) < 0, 2, 0) - 
    IF(OR(AND(WEEKDAY([Fecha cierre]) = 7, WEEKDAY([Fecha de inicio]) = 7), AND(WEEKDAY([Fecha cierre]) = 1, WEEKDAY([Fecha de inicio]) = 1)), 1, 0) - 
    IF(AND(WEEKDAY([Fecha de inicio]) = 1, (WEEKDAY([Fecha cierre]) - WEEKDAY([Fecha de inicio])) > 0), 1, 0) - 
    IF(AND(NOT(WEEKDAY([Fecha de inicio]) = 7), WEEKDAY([Fecha cierre]) = 7), 1, 0)
)

Resources