function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jimmy.juarez1.392248166112164E12jimmy.juarez1.392248166112164E12 

Calculate Date by Adding Date + Number in Business Days

I am attempting to create a workflow rule field update formula that will return/update a date.  This formula will take a given date (DATE 1) plus (+) a processing days number (NUMBER 1) and return/update a date (DATE 2) during the work week.


Field Definitions
DATE 1 = Given Date
NUMBER 1 = Processing Days
DATE 2 = Result
 
Basic Formula:
DATE 1 + NUMBER 1 = DATE 2

Example 1
If I set a date field to 11/4/2014 (Tuesday) and want to add 3 business days, it should return 11/7/14 (Friday).
​DATE 1: 11/4/14 (Tuesday)
NUMBER 1: 3 (Business Days)
DATE 2: 11/7/14 (Friday)
 
Example 2
If I set a date field to 11/4/2014 (Tuesday) and want to add 10 business days, it should return 11/18/14 (Tuesday).
​DATE 1: 11/4/14 (Tuesday)
NUMBER 1: 10 (Business Days)
DATE 2: 11/18/14 (Tuesday)
 
Please note, DATE 2 should always be during the week.  Any help would be greatly appreciated. Thank you!
 
BalajiRanganathanBalajiRanganathan

Iit is better if you can do this in trigger. You can have a new "Business Hours" in the setup. use addGmt method from BusinessHours Class.

Below is the sample code : 
BusinessHours bh = [SELECT Id FROM BusinessHours WHERE Name = 'new Business hours'];
DATE2 = BusinessHours.addGmt(bh.id, DATE 1, NUMBER 1 * 86400000);

If you still want to do with Formula, refer the link below

https://help.salesforce.com/HTViewSolution?id=000003920&language=en_US
 
Nihar SharmaNihar Sharma
Hey jimmy,

can you please share your code ?

i am facing this same scenario and i don't know how to do thi forward.

Regards,
Nihar
Neena K BainsNeena K Bains

Facing a similar issue and this is how I made it work --

Needed to calculate an "estimated close date" based on when a record was created.

Records are only created on weekdays.

The estimated close date needed to be three business days later and fall on a weekday.

Ideally, needed to be done through formula, as a default value for the estimated close date field.

I built this out --

 

IF(MOD(TODAY() - DATE(1900, 1, 6), 7) > 3, TODAY()+5, TODAY()+3)

Basically, if it's a
Monday, add 3 days
Tuesday, add 3 days.
Wednesday, add 5 days
Thursday, add 5 days
Friday, add 5 days

Based on this:
https://success.salesforce.com/ideaView?id=087300000006tam

Formula:
MOD( {!CloseDate} - DATE(1900, 1, 6), 7)
Remarks: 
This formula worksby picking a known Saturday (January 6, 1900 in the example), subtractingit from the provided date, and using MOD to divide the result by 7 andreturn the remainder. Thanks to Simon W. and Jesse C. for this example.
Exclude Saturday & Sunday from reports by adding a report selection criteria of "Day of Week greater than 2".
A text version of this formula could spell out the day of the week:
CASE(MOD( {!CloseDate} - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,"Friday","")

Neena K BainsNeena K Bains

And if you create records on weekends:

IF(MOD(TODAY() - DATE(1900, 1, 6), 7) > 3, TODAY()+5, (IF(MOD(TODAY() - DATE(1900, 1, 6), 7) = 0, TODAY()+4, TODAY()+3)))

Akhil AnilAkhil Anil
To know the detailed approach on how to add business days to a date, kindly refer the below blog post.

https://force-base.com/2016/11/05/how-to-add-business-days-to-a-date-in-salesforce-formula/
VFadminVFadmin
To add to Neena's wonderful formula:
Her assumption asserted the following: "The estimated close date needed to be three business days later". In other words it was 4 days after.
In her IF function the 2 options for returned dates were +5 and +3. Or: 4+1 and 4-1. If her assumptions were 8 days, then the returned dates would be 8+1 (+9) and 8-1 (+7).
Interestingly, if you want a certain amount of business days prior a date, then it becomes - #-1 and -#+3. (for #=8, 9 and 11).

I hope this helps further.
 
Art Ordoqui 1Art Ordoqui 1
Thanks Neena and VF Admin.  Do you know how to incorporate Holidays into your formulas? Is there a way to use the holidays from setup?
Craig POLLOK 2Craig POLLOK 2
Hey Team,
I have a very similar scenario but I am finding if a case is created outside of business hours it behaves unusually.
For example.
If it is created on Sunday at 7pm, I want it to have an expected close date of the following Monday. I have a formula which adds 8 days to the case date for sunday. However if the case is created on Sunday after 5pm it has an expected close date of the following tuesday. I am quite confused.