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.
Add a comment
|
2 Answers
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
answered Jan 11, 2012 at 13:05
-
That formula works for Saturdays and Sundays as off days, but not giving correct results for Thursdays and Fridays. Commented Jan 11, 2012 at 13:08
-
Sorry for misreading your question. So you are assuming weekend to be Thursday and Friday? Commented Jan 11, 2012 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.– SaschaCommented Apr 25, 2013 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! Commented Jan 11, 2012 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/… Commented Jan 12, 2012 at 7:17