Calculate Business Days between start and complete date
My client has a Human Resources (HR) form that is supposed to be completed within a 25 business day window. The goal for my C-level client is to have 80% of these forms throughout the year. Unfortunately the forms are created in another system, so all I’m building is a tracking list. To preserve the innocent business process I have recreated a generic version of the list for review.
Columns:
Division, Sub-Division, Form For, Form Number, Date Form Issued, Date Form Expires, 15 day Notes, Extension Request Date, New Expiration, Date Form Completed, Form Administrator, Business Days for Completion
INSERT IMAGE HERE
The basic formula to calculate the difference between two dates is =DATEDIF([Start Date],[Completed Date], “d”) This, however, does not take into consideration business days.
If found a great post – SharePoint List Calculated Column – Calculate Business Days that demonstrates how to calculate how many days are left until an item is due (not exactly what I was looking for).
Following the logic of that post I translated the calculated column into:
=(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
INSERT IMAGE HERE
How’d I do that? Well I found a great post Working Days, Weekdays and Holidays in SharePoint Calculated Columns that describes how the WEEKDAY formula works and understood that I just needed to change my references (and not touch the WEEKDAY function).
To track my client’s goal, I then created a KPI to determine the percentage of forms where the Business Days Completed were less than 26.
INSERT PICTURE HERE
This is not a perfect solution because it does not consider holidays, and of course my client is in government so there are a LOT of holidays. Luckily, if the goal of 80% is met with this calculation then it could only possible be higher if holidays were considered.
Images will come soon (Doing some maintenance on my sample Site Collection). Thanks to all my Twitter friends who helped.