I have found a formula that will calculate a due date based on business days using the following:

CASE(
MOD(Date_Assigned__c - DATE(1900, 1, 7), 7),
0, (Date_Assigned__c) + Estimated_Turnaround__c + FLOOR((Estimated_Turnaround__c -1)/5)*2,
1, (Date_Assigned__c) + Estimated_Turnaround__c + FLOOR((Estimated_Turnaround__c)/5)*2,
2, (Date_Assigned__c) + Estimated_Turnaround__c + FLOOR((Estimated_Turnaround__c +1)/5)*2,
3, (Date_Assigned__c) + Estimated_Turnaround__c + FLOOR((Estimated_Turnaround__c +2)/5)*2,
4, (Date_Assigned__c) + Estimated_Turnaround__c + FLOOR((Estimated_Turnaround__c +3)/5)*2,
5, (Date_Assigned__c) + Estimated_Turnaround__c + CEILING((Estimated_Turnaround__c)/5)*2,
6, (Date_Assigned__c) - IF ( Estimated_Turnaround__c > 0,1,0) + Estimated_Turnaround__c + CEILING((Estimated_Turnaround__c)/5)*2,
null)

What I want to do is use a similar formula with a datetime field and calculate the due date based on business hours instead of business days. For example, if it was Friday afternoon at 4pm and the Estimated_Turnaround__c field value was 8, then the due date would return the following Monday at 4pm. Or, if the value was 2, then the due date would return the following Monday at 8am. Is this possible using a formula? Or, would this need to be accomplished using APEX code?

Thanks for any suggestions.

Attribution to: Dman100

Possible Suggestion/Solution #1

If your hours follow a regular pattern it may be possible to create a formula...

But for these sort of calculation we have chosen to use the Apex BusinessHours class, as it is flexible in terms of the pattern of hours and also takes account of any Holidays setup in the org.

To use the BusinessHours.add method you need one or more "Business Hours" objects created using Setup -> Company Profile -> Business Hours, and you must pass the ID of one of these as the first argument. (They are named and you can nominate a default and query for the ID like any other object.)

For example, if a working day is 8 hours weekdays only, you might set the Business Hours to be 8am to 4pm for Monday to Friday and none on Saturday and Sunday.

If your turnaround time is in hours, you would need to multiple that value by (1000 * 60 * 60) before using it with the BusinessHours.add method as that works in milliseconds.

Attribution to: Keith C

Possible Suggestion/Solution #2

This is sample trigger for calculating business hours excluding weekends and holidays:-

trigger calcBusinessHours on Submitted_Order__c (before insert, before update) {

    // Assumes the BSC work hours are the default for the Org
    BusinessHours stdBusinessHours = [select id from businesshours where isDefault = true];

        for (Submitted_Order__c so : Trigger.new) {
            if ((so.WB_Submitted__c != NULL) && (stdBusinessHours != NULL)) {
            // BSC works 11.5 hours / day (8:00 AM - 7:30 PM, M-F). Our SLA is 2-days (23 business hours)
            so.SLA_Due_Date__c = BusinessHours.addGmt (stdBusinessHours.id, so.WB_Submitted__c, 23 * 60 * 60 * 1000L);
            }
      }
 }

Write a trigger for calculating due date excluding weekends and holidays. The formula provided by you excludes weekends but not holidays

Attribution to: Ram
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/34827