Using DATEDIF doesn't include first day (Calculating vacation days)

Iron Contributor

I have a SharePoint list generated by PowerAutomate to keep track of employee time off requests. I'm using two text columns called StartDate and EndDate. The column values look like 05/14/2024 00:00:00

 

I want a column to calculate how many days a person has taken off, minus weekends. A web search brought me to a Stack Overflow post from ten years ago that actually worked relatively well - I do not know what this means (is "FLOOR" relevant?). 

=DATEDIF(StartDate,EndDate,"D")-IF(WEEKDAY(EndDate)=7,FLOOR((DATEDIF(StartDate,EndDate,"D")+WEEKDAY(StartDate))/7,1)*2,FLOOR((DATEDIF(StartDate,EndDate,"D")+WEEKDAY(StartDate))/7,1)*2+1)+IF(WEEKDAY(StartDate)=7,2,1)

 

The issue is that, for example, if StartDate=05/30/2024 00:00:00 and EndDate=06/04/2024 00:00:00, the column is generating three days instead of four.  

 

Similarly, if one day is taken off (05/14/2024 00:00:00-05/14/2024 00:00:00), it's appearing as 0 instead of 1. 

 

Theoretically, I could have PowerAutomate fudge the data but I want the calculate column to rely on the StartDate and EndDate columns in case they're ever manually edited. And, although I'm not sure how, I could theoretically just add "1" to the column but I imagine there may be occasions that could be incorrect (ie., if someone mistakenly enters their vacation starting on a Sunday). 

 

Bonus: How could I add this condition to the formula? IF value of column Approved is "Reject", then 0. 

1 Reply

@Anthony-123 for inclusive working days excluding weekends I use:

=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)