+ Start a Discussion
MSheridanMSheridan 

Case Aging and Business Hours

    Hi All! I have two formulas. The first one I created to keep a running ticker of the number of days, hours, & minutes a case has been open. It looks like this:

Text(FLOOR(NOW() - CreatedDate)) &" "&"day(s)"&" "&Text(FLOOR(MOD( (NOW() - CreatedDate) * 24 ,24))) &" "&"hrs"&" "&Text( FLOOR(MOD(((NOW() - CreatedDate) * 24)*60 ,60)))&" "&"min")
The second formula I got off the boards and it keeps track in business hrs the number of hrs a case has been open:

IF(AND(MOD(TODAY()-DATE(1900,1,7),7)< (NOW() - CreatedDate ), ((NOW() - CreatedDate )<7)),((NOW() - CreatedDate)-2) * 24, ((NOW() - CreatedDate)-(2 * ((NOW() - CreatedDate)/7))) * 24)

Both of these formulas work great, however, I would like to have the business hrs formula in the same format as the first formula. So it would be business hrs in days, hrs, and minutes. Does anyone have any ideas?? I keep playing with it but it is always off.  
Best Answer chosen by Admin (Salesforce Developers) 
MackMack
Hi,
FYI: The second formula is incorrect - if you have two date/times which are one hour apart, it calculates them to be 0.7 hours apart.

All Answers

MackMack
Hi,
FYI: The second formula is incorrect - if you have two date/times which are one hour apart, it calculates them to be 0.7 hours apart.
This was selected as the best answer
bdowneybdowney

Mack,

 

Is there any update the the correct formula for the business hours calculation? You indicated:

 

 


Mack wrote:
Hi,
FYI: The second formula is incorrect - if you have two date/times which are one hour apart, it calculates them to be 0.7 hours apart.

 

Thanks!

 

bdowney

Orion@gmail.comOrion@gmail.com

The second formula from the Case Age package calculates by deduction not addition which is why the format is different. The formula counts backwards from 1 hour in the minutes rather than adding minutes to 1 hour.

mmrrmmrr

Can I please have the complete correct formula , I have the same requirement. please help!!