Elapsed Time between DateTime fields

Salesforce DateTime fields can capture when meaningful events occur on a record:  when it was created; when it was last updated; when a field changes to a specific value.  This post describes how you can extract the days, hours, and minutes between 2 DateTime fields in a user-friendly format.

For this post, I’ll use the Salesforce Case object and the standard field LastModifiedDate to describe an approach for finding out how long its been since each Case has been modified, and displaying the elapsed time in an easy-to-read format using formula fields.   To see the results of each step, you can create custom numeric formula fields and add them to your page layout.  You’ll want to remove them later and only use the final custom text formula field.

For starters, we can just subtract the current DateTime from the LastModifiedDate.

NOW() - LastModifiedDate

Next, we need to find how many hours are left over after the full days.  Since there are 1440 minutes in a day, we can multiply the original difference by 1440 to convert it from days into minutes.   The MOD() function returns the remainder of a division, so by taking the MOD() of the division by 1440, we’re left with just the minutes that don’t fit into full days.  Dividing those minutes by 60 (60 minutes in an hour), and using FLOOR() again, we get the number of full hours:

FLOOR( MOD((NOW() - LastModifiedDate) * 1440, 1440) / 60)

The last step is to figure out how many minutes are left over after the full hours.  Remember that FLOOR() returns the numbers to the left of the decimal, and MOD() returns whatever is left over.  We can use the same formula for minutes that we used for hours, except we’ll use MOD() instead of FLOOR() to get the remaining minutes.

MOD( MOD(Resolution_Calc__c * 1440, 1440), 60)

Putting it all together using the TEXT() function to convert each of the numeric parts to strings, and adding words creates an expression that is easy for the User to understand:

TEXT( FLOOR( Resolution_Calc__c ) ) + ' days ' +
TEXT( FLOOR( MOD(Resolution_Calc__c * 1440, 1440) / 60) ) + ' hours ' +
TEXT( ROUND(MOD( MOD(Resolution_Calc__c * 1440, 1440), 60), 2) ) + ' mins'
results in a custom field value of something like:

5 days 13 hours 17 mins
(Oh, yeah, I added the ROUND() function on the minutes to keep things neat).

You can learn about all the functions available in custom field formulas from the custom field formula edit screen.  Click on a function in the list to see a short description, or follow the ‘Help on this function’ link.