It would be nice to be able to see Case Duration on the Case page layout, so we can keep track of the time that the support rep has been working on the case.  It sounds like a quick and easy formula to subtract Date/Time Opened from Date/Time Closed, but the result might not satisfy many users.

When using a number formula with 2 decimal places to create a Duration field, Salesforce will give the result in numbers of days, including a fractional amount.  For example, if Date/Time Opened is 8/6/2012 6:13 AM and Date/Time Closed is 8/15/2012 12:05 PM, the duration will be 9.24 days.  A lot of people might mistranslate the numbers to mean 9 days and 24 hours, which could be interpreted as 10 days, but in fact 9.24 days is actually 9 days 5 hours and 52 minutes.  So how can we get a formula to express the duration in Days, Hours, and Minutes? First, we have to select “Text” for the Formula Return Type, then use the following formula:

1	TEXT(FLOOR(ClosedDate – CreatedDate)) & ” Days: ” &
2	TEXT(FLOOR(((ClosedDate – CreatedDate) – FLOOR(ClosedDate – CreatedDate)) * 24)) & ” Hrs: ” &
3	IF(
4	FLOOR(ClosedDate – CreatedDate) > 0,
5	TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”,
6	TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”)

Line 1 is for Days: TEXT(FLOOR(ClosedDate – CreatedDate)) & ” Days: “

I used FLOOR to round the number to the nearest integer and I used it for the number in days.

Line 2 is for Hrs: TEXT(FLOOR(((ClosedDate – CreatedDate) – FLOOR(ClosedDate – CreatedDate)) * 24)) & ” Hrs: “

I used the integer from Days “FLOOR(ClosedDate – CreatedDate)” and subtracted it from the total of “ClosedDate – CreatedDate” to get the remainder.  Then I multiplied the remainder with 24 to get the number in hours.

Line 3- 6 is for Mins: IF( FLOOR(ClosedDate – CreatedDate) > 0, TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”, TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”
)

I had to split the logic in two because if ClosedDate and CreatedDate are on the same day, that would return days in 0, which would cause an error in the formula when it tried to divide by 0.

Line 5 is to find Mins when Days is more than 0.  I used “((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24)))” to find the remainder after the hours and multiplied that by 60 to get minutes.

Line 6 is to find Mins when Days is less than 0.  I used “((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) , 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24)))” to find the remainder after the hours and multiplied that by 60 to get minutes. From the previous example (Date/Time Opened is 8/6/2012 6:13 AM and Date/Time Closed is 8/15/2012 12:05 PM), we should get the Duration to look like this 9 Days: 5 Hrs: 52 Mins.

A few caveats:

1. This formula is only for time duration between Opened and Closed (not elapsed time).
2. If you want to see elapsed time while the case is still open, you should create a new formula for Elapsed Time and replace ClosedDate with Now().
3. It doesn’t support Daylight Saving Time changes.  If a case was opened before the daylight saving began and it was closed after the daylight saving time, the formula result would be 1 hour less.  The same thing applies when the time is changed from daylight saving to standard time, but the result will be 1 hour more.
4. It doesn’t support “Business Hours”.  This is only for 24/7.

What other Datetime values might you use it for?