Forum Discussion
Sharepoint list calculated column difference between dates (no weekends)
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 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)
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)
)