How to get the business days excluding the weekends (Thursdays and Fridays) in between two given dates? Given dates are two list columns (Start Date & End Date) and business days is a calculated column.


One of the calculated column functions you can use in SharePoint is Weekday which returns 1 for Sunday, 2 for Monday and through to 7 for Saturday.

So your calculated column will become like this:

=(DATEDIF([Start Date],[Completed Date],”d”))-INT(DATEDIF([Start Date],[Completed Date],”d”)/7)*2-IF((WEEKDAY([Completed Date])-WEEKDAY([Start Date]))>0,2,0)+1
  • That formula works for Saturdays and Sundays as off days, but not giving correct results for Thursdays and Fridays.
    – Ahmed Atia
    Jan 11 '12 at 13:08
  • Sorry for misreading your question. So you are assuming weekend to be Thursday and Friday? Jan 11 '12 at 13:10
  • for Workingdays you must also keep attention about the bank holidays oder s.th. That could not ever be supported by an automatism i think.
    – Sascha
    Apr 25 '13 at 13:43
=IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

thank to Krishnan P

  • It's not working. One case, Start Date: 1/12/2012 and End Date: 1/14/2012, the number of business days is 1 although your formula result is 3 days!
    – Ahmed Atia
    Jan 11 '12 at 13:54
  • Sorry, Ahmed! I didn't carefully read yiu answer. For your situation solve is more complex. Unfortunaly SharePoint don't fully support [Excel weekday][1] function and you have to write convertion that will help you to calculatet suturday as first week day and friday as 7th week day. Or you can try to find or research another formula. [1]: office.microsoft.com/en-us/excel-help/…
    – IAfanasov
    Jan 12 '12 at 7:17

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.