Calculate business days in a formula field

Hi

I have created a workflow where if a tick box equals true a date field is populated with a date 18 days from today. What I am trying to achieve is that the 18 days to only reflect business days and not include Sat and Sun.

any suggestions?

August 29, 2014
·
Like
0
·
Follow
3

Best Answer chosen by Kyri T

Jason Curtis NBSFDG
Karanraj's link really only covers getting the # of business days between two exisitng values. You want to add 18 business days to an existing date. There is a good link here: https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#adding-business-days (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#adding-business-days)

And this takes you step by step: http://salesforce-shruthi.blogspot.com/2011/09/exclude-saturdays-and-sundays-while.html

I tested the below formula out and it works well. StartDate__c in your case would be TODAY(), and you could replace NumberOfDays__c with 18 (and just leave all of the other math in place if you don't fell like converting). Although, it might behove you to put the 18 value into a custom setting and then you can insert that field into the formula, and if your requirements ever change you won't need to touch the workflow.

Also, important to note that this won't take into account holidays.

CASE( MOD(StartDate__c - DATE(1900, 1, 7), 7), 0, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c-1)/5)*2, 1, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c)/5)*2, 2, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+1)/5)*2, 3, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+2)/5)*2, 4, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+3)/5)*2, 5, (StartDate__c) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2, 6, (StartDate__c) - IF(NumberOfDays__c>0,1,0) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2, null)
Good luck.