Blog

If you are faced with the challenge of calculating the duration of time between two dates in Salesforce, this post is for you. Lets say what you want is to take two dates and times, for example 20/01/2014 09:00am and 21/01/2014 10:20am and calculate the duration of time between them in hours and minutes, and display the result like this: 24:20 or 24h:20m.

If we just create a formula field and subtract the two dates, we will end up with something like this:

Using a straight subtraction Salesforce gives us the difference of the dates in days and fractions of days. In the example above, it is 44.96, almost 45 days.

To calculate and display the difference in hours and minutes, we have some work to do.

Firstly, lets calculate the number of hours difference between the two dates.
The formula for this calculation is simple, it just takes the difference of the two dates which is the number of days and multiplies by 24 to get the number of hours. It uses the FLOOR function to round down the result to the nearest integer, so if the difference is 44.96 like in the previous example, we want 44 days not 45 days to use in the calculation of the number of hours. We also use an IF statement to add a 0 to the result so we get '09' and not just '9' if the number of hours is less than 10.

Then the duration in minutes. This one is a little more complex. We use the MOD function to give us the remainder of a division, in this case the number of minutes that are left over. We also use the ROUND function to remove decimal points. The formula looks a bit more complex as we are firstly testing to see if the number of minutes is less than 10, and if so adding a '0' to the result so we get '07' and not just '7'.

Finally, lets put them together. This formula just puts the two previous formula fields together, and also replaces 0:0 (if there was no result) with 00:00.

And now in action. The first example is when the open and close dates are on the same days. The result is a calculation of 5 hours and 7 minutes. We have left the calculated fields for the hours and minutes on the page layout, just for demonstration purposes, you would remove them and just leave the final result.

The second example is when the open and close dates go over one day. Here the result is 25 hours and 5 minutes.

What Certification are you studying for now?

Focus on Force currently provides practice exams and study guides for sixteen certifications

View All Courses

Martin Gessner

Martin Gessner is the Founder of Focus on Force.

He has spent over 10 years working in various Salesforce roles including business analyst, project manager, consultant and solutions architect. Along the way he has earned twelve certifications, published "The Salesforce Career Playbook", and helps Salesforce professionals learn more about Salesforce, develop their career and prepare for certifications.

Subtract Two Date Fields to Calculate Duration of Time

Salesforce Roll-up Summary Using the Value of a Formula Field

Roll-up summary fields are a very useful Salesforce feature, but you may come across a situation where you need to rollup the value of a formula field. Here is an example: You have added a custom field to the Product object to record the cost of a product. You want the total cost of all products added displayed on an opportunity. In the screenshot below you can see Widget A… Read More

Saving Emails to Salesforce

If you use Gmail or another web based email service, Salesforce has a feature that will allow you to save your emails related to leads, contacts and open opportunities. It is the ‘Email to Salesforce’ features and when enabled, allows users to bcc a unique bcc email address to automatically create activity records against records in Salesforce. To use this feature, firstly the Salesforce Administrator has to enabled it, and… Read More

Comparing Salesforce Editions

There are a number of editions of Salesforce offering varying features and capabilities. If you are new to Salesforce and deciding which edition is right for you, then this guide will be helpful. If you already have Salesforce it will be useful to know what features you can take advantage of and any limitations you will have. It is of course possible to upgrade to a higher edition. If you… Read More

Salesforce Search Layouts

Salesforce gives up the ability to customize many areas of how it displays data. One area that is important to understand but easy to overlook is search layouts. Search layouts allow us to customize the fields both standard and custom that are displayed in various results lists. It is worth the time to customize, as it can improve productivity by ensuring that the most useful fields are displayed in the… Read More

Salesforce Instances vs Orgs vs Environments

Once you start getting involved with Salesforce, you will hear a number of terms such as Instance, Org, Environment and even POD. Its easy to get these confused or even not to even be aware of what they really mean. One person may refer to the development instance and another the development environment. Some organisations may refer to the fact that they have multiple instances of Salesforce, and another multiple… Read More

What is the Salesforce Account Hierarchy (And How To Use it)

The Salesforce account hierarchy allow accounts to be related in a hierarchy by using a ‘parent account’ field to link them together. Once linked, the complete hierarchy structure can be viewed and links are provided to navigate directly to the accounts.The first step is to create or identify the ultimate ‘Parent’ account. This is the account at the top of the hierarchy. In our example, the top level account is… Read More

Data Loader Tips – Inserting or Updating Blank Values in a Field

You may have come across the situation where you need to use Dataloader to clear or delete the value of a field. Normally, if you provide a blank value in the input file, DataLoader will ignore it and leave the field value as is. Lets look at an example. Here we have loaded data into the Product Description field instead of the Product Code field. Lets imagine, instead of 3… Read More

Salesforce Multicurrency – What You Need to Know

What is Salesforce multicurrency?Before we look at how multicurrency works and what you need to look out for, lets look at what it offers:Adds the ability to record amounts in different currencies. If you don’t have multicurrency enabled, then amounts are just displayed as an amount.Allows amounts to be converted, displayed and reported according to exchange ratesAllows the use of dated exchange rates so the value at… Read More

Preventing Duplicate Records with Salesforce Config

The Salesforce mantra is ‘clicks not code’ so we always look for a config solution to a problem before resorting to code. While we can set the unique indicator on a custom field to prevent duplicate values for one field across records, how do we prevent records inserted with duplicate values across all or some of the fields? Do we need to write a trigger to handle this? Or is… Read More

Salesforce Case Management

In most every company which prides itself on taking care of its customer base to the fullest, the Salesforce native functionality of Case Management goes hand in hand with customer success. The departments in an organization that deal with customer service, product support, issue resolution and service requests can all benefit from utilizing the case management functionalities; and related automation options; within Salesforce. As described in Salesforce Service Cloud Overview … Read More

Using DataLoader with Lookup Fields

External Ids provide a convenient way to integrate data from an external system, by allowing the use of a unique record identifer for records instead of the Salesforce Id. For example, you may define an Account Code external id on the Account object, which is the unique key for an account record in an ERP system that is integrated with Salesforce. When using DataLoader or another integration tool to update… Read More

Salesforce Mass Email

When you hear that Salesforce has a mass email capability you may think that you don’t need a marketing automation package to do that for you. Dig a little deeper and you will find that although Salesforce can send mass emails, there are a number of limitations that may have you looking back at AppExchange products. Firstly, lets look at what you can do with Salesforce mass email capability. You… Read More

Quick Start Guide to using Conga Composer with Excel

Conga Composer is one of the most widely used 3rd party AppExchange apps for document generation with Salesforce. It can be used to merge Salesforce data to produce documents in Word, Excel, Powerpoint, HTML Email and PDF format. Here is a quick guide to generating an Excel spreadsheet with data from Salesforce. Create Your Excel Template Define the Fields and Reports it will use Add Merge Fields to the Excel… Read More

«‹18 192021 ›

@