+ Start a Discussion
MC34MC34 

Formula for calculating time difference in hours?

Hi there, 

I have been working with a formula field (data type -NUMBER) Business_Hours_Age__c for calculating the time difference between two dates. Here is the formula.
 
IF(DATEVALUE(Aging_Reopened__c) = DATEVALUE(ClosedDate),  
(ClosedDate - Aging_Reopened__c) * 24,  
((CASE(MOD(DATEVALUE(CreatedDate) - DATE(1985,6,24),7),  
0 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),  
1 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),  
2 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),  
3 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),  
4 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),  
5 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),  
6 , CASE( MOD(DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),  
999)  
+ (FLOOR((DATEVALUE(ClosedDate) - DATEVALUE(Aging_Reopened__c))/7)*5)  
- 2) * 9)
+  
MAX((  
DATETIMEVALUE(TEXT(YEAR(DATEVALUE(Aging_Reopened__c))) & "-" & TEXT(MONTH(DATEVALUE(Aging_Reopened__c))) & "-" & TEXT(DAY(DATEVALUE(Aging_Reopened__c))) & " 01:00:00am") - Aging_Reopened__c+ 1) * 24, 0))  
+  
MAX((Aging_Reopened__c-  
DATETIMEVALUE(TEXT(YEAR(DATEVALUE(Aging_Reopened__c))) & "-" & TEXT(MONTH(DATEVALUE(Aging_Reopened__c))) & "-" & TEXT(DAY(DATEVALUE(Aging_Reopened__c))) & " 16:00:00")) * 24, 0)
Included in the above formula, Aging_Reopened__c is date time formula field that gives the value of date/time when closed case is reopened by the rep or created date. 
BLANKVALUE(ReOpen_Date__c, CreatedDate)
The idea for Business_Hours_Age__c formula is to give the case age in hours and reset the age to start when case is REOPENED. For example, if case age is 35 hours at the time of close, and it is then reopened the ticker starts from the time it is reopened. 

The formula works fine for most part. However, looking at some values I am not convinced that formula working as expected. What I am missing? Is GMT/EST conversion causing this? 

User-added image

Also, is there  a way to add format to the formula for HH:MM:SS so it returns the value in this fashion? 

Any help is greatly appreciated. 

Thank you, MC 

 
ShivankurShivankur (Salesforce Developers) 
Hi MC,

You can use simpler formula to achieve the similar thing.

Please check out below link to understand similar implementation:
https://focusonforce.com/configuration/subtract-two-date-fields-to-calculate-duration-of-time/

Hope above information helps. Please mark as Best Answer so that it can help others in future.

Thanks.