Ever needed to work out how many business days exist between one date and another? Look no further! The below calculation came about as part of a need to calculate case deadlines based on business days only (i.e excluding weekends).
In this article we’ll look at an easy way to use a calculated column to work out the total number of business days between two dates.
To work out the number of business days between two dates in SharePoint simply create a new field in your list and assigned it the “Calculated Column” data type, in the formula field add in the following code changing the “Start Date” and “End date” text to match the two dates you want to compare for business days and then set “The data type returned from this formula is:” to be “Number”
[javascript]
=IF(ISERROR(DATEDIF([Start Date], [End Date], "d")), "", (DATEDIF([Start Date], [End Date], "d")) + 1 – INT(DATEDIF([Start Date], [End Date], "d") / 7) * 2 – IF((WEEKDAY([End Date]) – WEEKDAY([Start Date])) < 0, 2, 0) – IF(OR(AND(WEEKDAY([End Date]) = 7, WEEKDAY([Start Date]) = 7), AND(WEEKDAY([End Date]) = 1, WEEKDAY([Start Date]) = 1)), 1, 0) – IF(AND(WEEKDAY([Start Date]) = 1, (WEEKDAY([End Date]) – WEEKDAY([Start Date])) > 0), 1, 0) – IF(AND(NOT(WEEKDAY([Start Date]) = 7), WEEKDAY([End Date]) = 7), 1, 0))
[/javascript]
Save the changes to your column and go back to your list view, create a new item with the two “Start Date” and “End date” (matching your actual names) filled in, the newly created calculated field should now show the correct number of business days in that date/time period
