Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Hermann Ouré

# Sample code to calculate target date excluding business days and holidays

Hello,
How can I calclulate a Due Date / target date excluding business Days and Holidays?
I have a date/time field called "First_Reply_Date__c" and when its filled, I would like the "Target_Date__c" field to be updated with the next working day.
For example, let's say the "First_Reply_Date__c" field is filled on a Friday
Then "Target_Date__c" will be on the Monday 28/10/2020 1:00pm

How could I do it using a code?
Thanks,
Abhishek (Salesforce Developers)
If you want to achieve deducting business hours using a formula field, or using a formula in a workflow to update a field, use the formula provided below. This formula gives the number of business hours rounded to the nearest hour, so you lose minute precision. However, you can probably adjust the formula to give minutes as well.

ROUND( 8 * (
( 5 * FLOOR( ( DATEVALUE( dateTime__1 ) - DATE( 1900, 1, 8) ) /
7) +
MIN(5,
MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) /
7) +
MIN( 5,
MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
)
)
),
0 )

Source (https://resources.docs.salesforce.com/218/latest/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf).

If you are comfortable writing an Apex class to return the business hours, you can very easily accomplish this. Just go to setup and search Business Hours. Enter your business hours and even holidays. Now, create an Apex class that calls the diff() method of the BusinessHours class to get the difference between two times while deducting your business hours. That easy.

The signature of the diff(Id, DateTime, DateTime) method is:
where startDate and endDate are DateTime fields and the difference in milliseconds is returned in a Long.

Example of method that calculates business minutes between two DateTimes using the BusinessHours class and diff(Id, DateTIme, DateTime) method:

public static Integer GetElapsedBusinessMinutes(Datetime startTime, Datetime endTime) {
if(startTime == null || endTime == null || startTime == endTime) {
return 0;
} else {
Long elapsedMilliseconds = BusinessHours.diff(bh.Id, startTime, endTime);
Long elapsedSeconds = elapsedMilliseconds / 1000;
Long elapsedMinutes =  elapsedSeconds / 60;
// Don't return negative values.
if(elapsedMinutes < 0){
return 0;
} else {
return elapsedMinutes.intValue();
}
}
}

For further reference, you can check this (https://sfdcmonkey.com/2020/06/15/business-date-holidays-lwc/).

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks.
Hermann Ouré
Hello Abhishek,
Thanks for your reply but the first formula is not quite what I need as it calculate business days between 2 dates  and I need to get a resolution target date.
But I found what is was looking for.
date_field__c +
CASE( WEEKDAY DATEVALUE(date_field__c)),
1, 1,
2, 1,
3, 1,
4, 1,
5, 1,
6, 3,
7, 2,
0)