ShowAll Questionssorted byDate Posted
MC34

# 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?

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

Shivankur (Salesforce Developers)
Hi MC,

You can use simpler formula to achieve the similar thing.