Danushka Silva

It's all about SharePoint

Calculate business days (weekdays) for a given two days using OOTB calculated field.

Recently I needed to calculate business days for a given start and end days. But I needed to do this using OOTB calculated field. This formula has to remove Saturdays and Sundays (Weekends) from the given start and end dates. Here I check if dates are inserted. If not I return an empty string. I was struggling with functions in calculated fields and I was not allowed to use SharePoint object model. I tried so many ways and finally came up with the correct formula. I need to thank Sujeewa, who helped me out with the formula. Here is the formula. Happy coding smile_wink

=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))


Please add a comment if you use this code or it helped you. So I can make my posts better.