[SalesForce] Calculating date/time x working hours from now

I need to trigger an email notification 9 working hours after a particular field is changed in Salesforce. So if the field is changed on a Friday at 4pm, I need to calculate now() + 7 hours + an extra 48 (working hours are between 9-6).

I want to insert the date/time, when the notification should be sent, in a field called SLA Deadline.

When the field is changed the notification will be scheduled to be sent 0 hours after the SLA Deadline date/time, using the Process Builder, as long as the other Process Builder criteria are met.

If the change is made after 6pm then the date/time would be 6pm the following day (unless the change was made on a Friday – in which case it would be 6pm the following Monday). If the change is made before 9am, then the date/time would be 6pm that day.

I was hoping to use a formula to calculate the value and then insert it using a field update. But if this isn't possible then I'm happy to use code instead.

Best Answer

The easiest way to do this via trigger would be to use Business hours. In this case it would be:

BusinessHours bh = [select Id from BusinessHours where IsDefault=true];
obj.SLA_deadline__c = BusinessHours.add(bh.Id, Datetime.Now(), 32400000);

Related Solutions

[SalesForce] Date is showing a different date than the Date/Time Field

The datetime in salesforce is always stored in GMT (UTC). The time is corrected to the users time zone when displaying it on the UI. The time is only corrected in the UI, so you need to make the adjustment yourself for formula fields.

Datetime input field cast to another timezone

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm

You need to adjust for your own time zone in your formula to account for this. In my formula fields, since I am in EST, I have to adjust all the datetimes by 5 hours to make sure I have the correct values. I just do this in a long case statement like below. this formula finds the hour of the day that a case was created, notice I have to adjust each time by 5 hours to make it work

TEXT(CASE(VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 
00,19, 
01,20, 
02,21, 
03,22, 
04,23, 
05,00, 
06,01, 
07,02, 
08,03, 
09,04, 
10,05, 
11,06, 
12,07, 
13,08, 
14,09, 
15,10, 
16,11, 
17,12, 
18,13, 
19,14, 
20,15, 
21,16, 
22,17, 
23,18, 
00))

[SalesForce] how to calculate due date in business hours

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

Best Answer

Related Solutions

[SalesForce] Date is showing a different date than the Date/Time Field

[SalesForce] how to calculate due date in business hours

Related Topic