+ Start a Discussion
rsmithdevrsmithdev 

Formula to calculate the business hours age of case.

1.Create a field for business days using a number formula field. Use the following formula to calculate minus weekends.

 

CASE( 
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2, 
null)

 

 

2.Create a date/time field for the current date/time 

 

3.Create workflow rule to update this field every time it is edited with formula below

 

Now()

 

 

4.Create a number formula field to calculate the age difference of the case today. I used the formula

 

VALUE( MID( TEXT( NOW__c ), 12, 2 ) ) - VALUE( MID( TEXT( CreatedDate ), 12, 2 ) )

 

 

5.Create a number formula field for the business hours of the company ie. 8 hrs

 

 

 

6.Create a number formula field to calculate the total age of the case in business hours

 

(business_days__c * business_hours ) + age_difference_of_case

 

DeptonDepton

Do you guys got this working?

 

I have some dates correct but when looking 2 days forward instead of getting for isntance 48 y get 4?

 

Thank you!

TNiemanTNieman
Why do you need #3, can't #2 just be a formula?