Timesaver #8 – Calculating Business Days

Hey all,

So, on Twitter this week Jon Adams from Florida asked a question using the #askforce hash – “Is there a way to replicate the WORKDAY function in Excel in SFDC?”.  My first response was to send him as link to an answer on the Salesforce Community page which laid out how to calculate the number of business days between two date fields.  It’s a great formula to have in your back pocket, but it wasn’t exactly what he had in mind.  What if you had a Start Date, and knew that you wanted something to run for 26 business days which populated via a number field?

So, later that evening, I sat down to see what I could come up with.  The idea actually would save me a lot of time in the professional services field.  If someone from xLerate was going to be on a project for 13 days (business days), I wouldn’t have to play the calendar finger dance we do when we are attempting to figure this out.

To do this, I went on a custom object called Projects (you could easily use any native object you wanted as well…).  There is a field called Start Date, which I have used for the beginning date that we are attempting to calculate from.  There is also a field called “Number of Business Days” which has an API name of BizDays.  This is the number of business days you wish to count up to.

Next you will need to create a formula date field, which I have called End Date.  The formula would be as follows:

CASE(
MOD(Start_Date__c – DATE(1900, 1, 7), 7),
0, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c-1)/5)*2,
1, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c)/5)*2,
2, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+1)/5)*2,
3, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+2)/5)*2,
4, (Start_Date__c) + BizDays__c + FLOOR((BizDays__c+3)/5)*2,
5, (Start_Date__c) + BizDays__c + CEILING((BizDays__c)/5)*2,
6, (Start_Date__c) – IF(BizDays__c>0,1,0) + BizDays__c + CEILING((BizDays__c)/5)*2,
null)

From this, the End Date will be calculated to the amount of business days (Mondays through to Fridays) stated in the field Business Days using the Start Date.

A caveat though – each country has separate and distinct holidays, so no holidays are added in this formula.  I am sure this could be calculated using a custom object and Apex, but for clicks not code administrators, this should assist.

How could I see this being used? Well, from a PS standpoint, it would be great for working out an end date of a project knowing the number of days that someone was to be working.  Or what if you wanted to know the date where you should be finishing an opportunity stage (above and beyond a Close date – say you wanted an opportunity to be closed if it took more than 30 business days to get to a 50% probability or above.

Hopefully you find this of assistance – if you think of other ways this could be use, let me know!