Using DATEDIF doesn’t include first day (Calculating vacation days)
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.
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. Read More