You need to sign in to do that
Don't have an account?
jimmy.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!
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!
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
can you please share your code ?
i am facing this same scenario and i don't know how to do thi forward.
Regards,
Nihar
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","")
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)))
https://force-base.com/2016/11/05/how-to-add-business-days-to-a-date-in-salesforce-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.
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.