Forum Discussion
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(OR(WEEKDAY([Fecha de inicio]) = 7, WEEKDAY([Fecha de inicio]) = 1), 1, 0) - IF(OR(WEEKDAY(IF(ISBLANK([Fecha cierre]), TODAY(), [Fecha cierre])) = 7, WEEKDAY(IF(ISBLANK([Fecha cierre]), TODAY(), [Fecha cierre])) = 1), 1, 0)So I have this formula for a calculated column that in theory should do all of this:
- Calculate the difference between two date columns, but if "Fecha cierre" doesn't have a value, it will calculate between "Fecha de inicio" and today. That way there will be no error should "Fecha cierre" is blank, which in most cases it is.
- This calculation subtracts the weekends (not public holidays)
For some reason, this formula is always adding 2 more days than necesarry. For example, 15/02/2024 until today (25/03/2024) is coming as 30, when it should be 28. I thought about adding a "-2" at the end, but this will come up as "-1" or "-2" if the start date is today or yesterday.
So.... How would the correct formula be?
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)
2 Replies
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) )
