Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

share|improve this question

2 Answers

up vote 0 down vote

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
share|improve this answer
That formula works for Saturdays and Sundays as off days, but not giving correct results for Thursdays and Fridays. – Ahmed Jan 11 '12 at 13:08
Sorry for misreading your question. So you are assuming weekend to be Thursday and Friday? – Falak Mahmood 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 at 13:43
up vote 0 down vote 
=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

share|improve this answer
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 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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