Mar 25 2024 01:08 AM
=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:
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?
Mar 25 2024 01:35 AM
Solution@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)
Mar 25 2024 01:46 AM
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)
)
Mar 25 2024 01:35 AM
Solution@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)