0

In SP2013 calendar list I have calculated column that calculates number of working days between two dates. It gives accurate number of working days until user puts weekend day as End date, than it calculates one more day (Saturday as working day). Code for calculated column is bellow:

=(DATEDIF([Start Time],[End Time],"D"))-INT(DATEDIF([Start Time],[End Time],"D")/7)*2-IF((WEEKDAY([End Time])-WEEKDAY([Start Time]))<0,2,0)+1

Any advice what to change in formula or if you know where can I find working one?

0

1 Answer 1

4

Try this formula

IF(ISERROR(DATEDIF([StartDate],[EndDate],”d”)),””,(DATEDIF([StartDate],[EndDate],”d”))+1-INT(DATEDIF([StartDate],[EndDate],”d”)/7)*2-IF((WEEKDAY([EndDate])-WEEKDAY([StartDate]))<0,2,0)-IF(OR(AND(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=7),AND(WEEKDAY([EndDate])=1,WEEKDAY([StartDate])=1)),1,0)-IF(AND(WEEKDAY([StartDate])=1,(WEEKDAY([EndDate])-WEEKDAY([StartDate]))>0),1,0)-IF(AND(NOT(WEEKDAY([StartDate])=7),WEEKDAY([EndDate])=7),1,0))
8
  • It still gives me incorrect answer.
    – Danilo
    Oct 25, 2016 at 13:59
  • It is working fine, I tested it. Do you not want it to count the day it starts? e.g. Tues to Friday is 4 days, and Tues to next Tues is 6 days. Oct 25, 2016 at 14:10
  • 1
    If I put from Tuesday to Saturday it counts as 5 working days, but it should count 4. So it is not working for me.
    – Danilo
    Oct 26, 2016 at 6:36
  • 3
    I updated the formula above to take care of that case. Nice catch :) Oct 26, 2016 at 13:22
  • 2
    actually, just tested it and added 1 between the "" and it works :) haah fantasticooooooooooooooooo
    – Bucki
    Feb 14, 2018 at 17:11

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