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?

|improve this question
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))
|improve this answer
  • It still gives me incorrect answer. – Danilo Oct 25 '16 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. – Ransher Singh Oct 25 '16 at 14:10
  • 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 '16 at 6:36
  • 2
    I updated the formula above to take care of that case. Nice catch :) – Ransher Singh Oct 26 '16 at 13:22
  • 2
    actually, just tested it and added 1 between the "" and it works :) haah fantasticooooooooooooooooo – Bucki Feb 14 '18 at 17:11

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