Print this page

How to express the difference of 2 Date/Time fields in days,hours,mn format ?

Knowledge Article Number 000004393
Description

The following is an explanation of how to express the difference of 2 Date/Time fields in days,hours,mn format within a custom field of type formula.

 

Resolution

This formula field is equivalent to the case age, subtracting created date from last modified date.

The problem is that date/time fields are written as numbers in the database, so simply subtracting them returns a number whose integer value is the number of days, and its decimals are hours and minutes expressed as a fraction of 1 day.

  • The first step is to create a formula field, labeled "D2subD1". This field should not be included on a page layout and it's return type should be number. This will be used to calculate the difference between the two dates, for instance:

{!LastModifiedDate} - {!CreatedDate}

 

  • Then create another formula field, visible to users, of type text, that transforms the result of the first formula into a nicer expression:

IF (D2subD1__c > 0, /*check to see if D2subD1 is greater than zero*/

TEXT(FLOOR( D2subD1__c)) & " days " & /*returns the number of days in D2subD1*/

TEXT(FLOOR( 24 * (D2subD1__c - FLOOR(D2subD1__c) ))) & " hours " & /*returns the remaining number of hours in D2subD1*/

TEXT(ROUND(60 * (ROUND( 24 * ( D2subD1__c - FLOOR(D2subD1__c) ),8) - FLOOR(ROUND(24 * (D2subD1__c - FLOOR(D2subD1__c) ),8)) ),0)) & " mins" /*returns the remaining number of minutes in D2subD1*/

, "")





promote demote