[SalesForce] how to calculate due date in business hours

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.

Best Answer

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

Related Topic