Time Difference in SP Calculated Column excluding weekends

Question

I have two dates (Initial Date & End Date) both date and time format in a SP List. I need to calculate the amount of days between both of them excluding weekends.

I already have the formula in a calculated column(using SP 2013 and Network Days is not supported), but it is not populating the correct time;

for example:

Initial Date: 28/04/2016 09:21
End Date: 28/04/2016 10:21

It gives me as result 1 (a complete day) but it actually was just one hour.

This is the formula, I'm using:

=IF(InitialDate<>"";IF(EndDate<>"";IF(AND((WEEKDAY(EndDate;2))<(WEEKDAY(InitialDate;2));((WEEKDAY(InitialDate;2))-(WEEKDAY(EndDate;2)))>1);(((DATEDIF(InitialDate;EndDate;"D")+1))-(FLOOR((DATEDIF(InitialDate;EndDate;"D")+1)/7;1)*2)-2);(((DATEDIF(InitialDate;EndDate;"D")+1))-(FLOOR((DATEDIF(InitialDate;EndDate;"D")+1)/7;1)*2)));IF(AND((WEEKDAY(TODAY();2))<(WEEKDAY(InitialDate;2));((WEEKDAY(InitialDate;2))-(WEEKDAY(TODAY();2)))>1);(((DATEDIF(InitialDate;TODAY();"D")+1))-(FLOOR((DATEDIF(InitialDate;TODAY();"D")+1)/7;1)*2)-2);(((DATEDIF(InitialDate;TODAY();"D")+1))-(FLOOR((DATEDIF(InitialDate;TODAY();"D")+1)/7;1)*2))));0)

Any idea?

Answer 1

It is not the datediff that is causing the problem. That function does return 0 when you have the same date with different times.

I am not familiar with the formula that you used but I noticed that it is adding 1 day to the datediff result. Tested the formula without the +1 and it looks OK. Give it a try (replace the column names before, and I had to replace ';' by "," to get the formula working on my environment):

=IF(startdate<>"",IF(enddate<>"",IF(AND((WEEKDAY(enddate,2))<(WEEKDAY(startdate,2)),((WEEKDAY(startdate,2))-(WEEKDAY(enddate,2)))>1),(((DATEDIF(startdate,enddate,"D")))-(FLOOR((DATEDIF(startdate,enddate,"D"))/7,1)*2)-2),(((DATEDIF(startdate,enddate,"D")))-(FLOOR((DATEDIF(startdate,enddate,"D"))/7,1)*2))),IF(AND((WEEKDAY(TODAY(),2))<(WEEKDAY(startdate,2)),((WEEKDAY(startdate,2))-(WEEKDAY(TODAY(),2)))>1),(((DATEDIF(startdate,TODAY(),"D")+1))-(FLOOR((DATEDIF(startdate,TODAY(),"D")+1)/7,1)*2)-2),(((DATEDIF(startdate,TODAY(),"D")+1))-(FLOOR((DATEDIF(startdate,TODAY(),"D")+1)/7,1)*2)))),0)