ShowAll Questionssorted byDate Posted
Kyri T

# 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?
Best Answer chosen by Kyri T
Jason Curtis NBSFDG

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.

Karanraj
Here is the link to implement business days in formula fields https://help.salesforce.com/HTViewSolution?id=000003920&language=en_US
Jason Curtis NBSFDG

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.
This was selected as the best answer
Gaurav Nirwal