Salesforce | The Number of Business Hours Between Two Date/Time Fields

At times there is a need to calculate business hours spent on certain things in Salesforce. In following guide I’ll explain how you can get the difference between two date/time fields relatively to your work hours and holiday schedule.

Calculate Business Hours by Using Formula Fields

Let’s start from the basics. For a simple calculation between two dates you could use the  following:

Formula above will return the number difference in days.

If you need to calculate the difference between two date/time fields you could use this:

If you need to calculate business hours between two date/time fields the Salesforce formula becomes more complex.

Here is how it looks:

The good thing is that it’s easy to implement. You just need to replace ‘date/time‘ fields with yours.

All the above formulas are explained in developer documentation that can be found here. Please take a moment to read and get familiar.

However, there are disadvantages to using such approach. The last formula does not take into consideration holidays, time zones, different work hours (in case you have multiple locations).

Luckily there is a way around it. It is more complex, however, the results are more accurate. Instead of the formula field, we’ll use Apex Trigger. The BusinessHours Class should take care of all calculations. We just need to ensure that the data is valid.

For more info on BusinessHours class please click here.

First, let’s add business hours to Salesforce. To do so, you need to go Setup -> Start typing ‘business’ in search bar.

Go ahead a click New Business Hours button. New window will open for you to specify the name, time zone and work hours for each day.

For our example, let’s add one Business Hours Record and call it ‘Main Office‘. Next we need to add holidays. To do so, go to Setup -> Type in search bar ‘Holidays‘.

Click ‘New’ button to add all necessary holidays. For each holiday there is an option to set it as recurring holiday. All the rest holidays that have different date each year need to be added separately.

The last step here is to assign holidays to business hours. You just need to go back to business hours record that you’ve created earlier and add holidays. Go ahead and click Add/Remove button under Holidays related list to assign holidays to business hours.

Calculate Business Hours by Using Trigger (Single Location)

Once all set, we are ready to move forward with our trigger. Let’s say we need to know how much time is spent on a case during business hours.

We’ll use the following fields:

• Date/Time Open (API – CreatedDate)
• Date/Time Closed (API – ClosedDate)
• Elapsed Time (API – Elapsed_Time__c)

For this example, our trigger will work on before update event type. At first, we need to select our default business hours record. If it has been found, it’s safe to proceed with the trigger.

Since Case Date/Time Opened field is actually CreatedDate, there is no need to validate if it’s empty. The date will be auto-generated when the case is created. We just need to make sure that Date/Time Closed (API – ClosedDate) is not empty and is updated. We don’t want a trigger to fire for other updates to a case.

Next, we’ll use BusinessHours Class to calculate time. The end result will be saved in the Elapsed Time custom field. Since the trigger is set on ‘before update‘, there is no need to perform DML operation.

Trigger with comments is listed below:

Calculate Business Hours by Using Trigger (Multiple Locations)

Let’s take another example with multiple business hours. This is helpful if you have different locations. Also, let’s say we want to calculate business hours on update & insert .

The ‘Case’ object will not be best for this example, so let’s just use a custom object. Each record should be tied to a specific location. That way it will be possible to calculate business hours for each place. If you use the same locations for other objects, it is wise to use a lookup that points to a location record itself. If not, the location field can be a simple picklist.

• Object name: Custom Object (API Custom_Object__c);
• Date/Time field: Start Time (API Start_Time__c);
• Date/Time field: Completed Time (API Completed_Time__c);
• Date/Time field: Hours Spent (Hours_Spent__c);
• Picklist field: Location (API Location__c) OR Formula Text field: (API Location__c). A formula should return location name as text. For example: Location__r.Name

Let’s also add business hours for different locations. For example: Chicago, New York, Lost Angeles. Please note that Location names should match Business Hours names.

The trigger itself will be very similar to the previous version.  The core logic stays the same, we just add separate rules for insert and update operations. Also, we add Map to hold all of our business hour records. The key for this map will be the location name. As you remember, we name business hours the same as locations. That way we can easily cross-reference location to business hours record and do the math.

If its insert, we want to make sure that Start Time and Completed Time fields are not empty. Because it will not make sense to do the calculation. For update operation, we also check if fields are not NULL and if they were changed.

Please take a look at trigger below. Comments should help you out with understanding the flow.

The trigger is ready to go. You just need to replace object and field APIs with your’s. I’m hoping you’ve learned something new today. Now you should be able to calculate number of business hours spent between two date/time fields in Salesforce. Please let me know if there is an easier way to do this. Feel free to post your suggestions!

Test Class Update:

Here is a very basic test class that I’ve put together. As is it will cover about 57% of the code.

The reason is that ClosedDate on the Case object won’t populate when changing the Status field on ‘before update‘ trigger. The simplest workaround would be to set a trigger to work on ‘after update‘. But in this case, you’ll have to consume SOQL query to get the case record & perform DML (update operation).

Feel free to comment if you have any suggestions!