Case Age Calculation - Approaches

on

1. Calculate Days the Case(s) Open Excluding Weekends

Custom case field -

IF( 

IsClosed,(5 * ( FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7 ) ) ) 

- 

(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) ), 

(5 * ( FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) ) 

- 

(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) ) )


2. Formula for days since Case Closed/Won -

Create Formula Field with Return Type as Number

TODAY() - CloseDate


3. Calculate difference between Case Open and Case Close Date -

IF(IsClosed , IF( MINUTE(TIMEVALUE(ClosedDate)) >= MINUTE(TIMEVALUE(CreatedDate )),(HOUR(TIMEVALUE(ClosedDate))- HOUR(TIMEVALUE(CreatedDate))) +  ((MINUTE(TIMEVALUE(ClosedDate)) - MINUTE(TIMEVALUE(CreatedDate )))/100),IF(MINUTE(TIMEVALUE(ClosedDate)) < MINUTE(TIMEVALUE(CreatedDate )),

 (HOUR(TIMEVALUE(ClosedDate))- HOUR(TIMEVALUE(CreatedDate))-1) +  (((MINUTE(TIMEVALUE(ClosedDate))+60) - MINUTE(TIMEVALUE(CreatedDate )))/100),

NULL)),NULL)


4. Calculate Case Age in Reports

In Case Object reports, 

To calculate time has elapsed from Case creation to present

Days - NOW()-CreatedDate 

Hours - (NOW() -CreatedDate )*24

Minutes - (NOW() -CreatedDate )*1440


We have to create Custom Formula Field to return the Age in Days, Hours, or Minutes (Non-Rounded Up Values)

1. Create a new Custom Field with the 'Formula' data type.

Click Setup |  Object Manager tab | Case | Fields & Relationships | New | Formula

2. In 'Field Label' enter Case Age in (Hours / Minutes / Days), select Number then click Next.

3. Select the Formula options below (depending on your requirements):

Case Age in Days - IF (IsClosed,ClosedDate - CreatedDate,NOW() - CreatedDate)

Case Age in Hours - IF( IsClosed , (ClosedDate - CreatedDate) *24, (NOW() - CreatedDate ) *24)

Case Age in Minutes - IF( IsClosed , (ClosedDate - CreatedDate) *1440, (NOW() - CreatedDate ) *1440)


Reference-

https://help.salesforce.com/s/articleView?id=000341512&type=1

Comments

Post a Comment