Print this page

I need to have formula that would show the elapsed time between two "Date/Time" fields

Knowledge Article Number 000220925
Description How can I create a formula field/s that would show the elapsed time (difference) between two "Date/Time" fields?
Resolution You would need to create 3 formula fields (please see reference formula below):

Note: Start time and End time field may vary based on your object.

1. Hours (sample name: SFDC_Case_Duration_HOURS__c)

IF( 
FLOOR(( End_Time__c - Start_Time__c )*24) > 9, 
TEXT(FLOOR(( End_Time__c - Start_Time__c )* 24)), 
"0" & TEXT(FLOOR(( End_Time__c - Start_Time__c )*24)) 
)

2. Minutes (sample name: SFDC_Case_Duration_MINUTES__c)

IF( 

ROUND(MOD(( End_Time__c - Start_Time__c )*1440,60),0) > 9, 
TEXT(ROUND(MOD(( End_Time__c - Start_Time__c)*1440,60),0)), 
"0" & TEXT(ROUND(MOD(( End_Time__c - Start_Time__c )*1440,60),0)) 
)

3. Combine the two formula fields

IF( 
SFDC_Case_Duration_HOURS__c & "," & SFDC_Case_Duration_MINUTES__c = "0:0", "00:00", SFDC_Case_Duration_HOURS__c & ":" & SFDC_Case_Duration_MINUTES__c)

Note: Doing the above steps will provide an output in the 00:00 format.




promote demote