0

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?

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.