Pages

Thursday, October 26, 2023

Salesforce: Case Age

In the Salesforce report, we can quickly add the 'Age' field to the report; it is even more flexible, where we can select the "unit" in Minutes, Hours, and Days. But before we discuss this further, let us agree on what is Case Age? 

  • For open cases, the elapsed time from creation to the present
  • For closed cases, the elapsed time from creation to the closing time of the case

Because 'Age' in the report is not an actual field, we cannot show it in the page layout or list view, to get this, we can create a custom Age field with the following formulas:

  • 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)

But what if we want to exclude the weekends? Here is the formula to calculate case age without weekends in Days:

IF (IsClosed,
IF ((CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Datevalue(ClosedDate) - Datevalue(CreatedDate))/7)*5)
- 1) < 0,
0,
CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Datevalue(ClosedDate) - Datevalue(CreatedDate))/7)*5)
- 1),
IF ((CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Today() - Datevalue(CreatedDate))/7)*5)
- 1) < 0,
0,
CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Today() - Datevalue(CreatedDate))/7)*5)
- 1)
)

Some example: 


Questions:
  • Will the calculation include decimal points for time differences? No, unless someone has an updated formula to include the time
  • Will the calculation honor the user's timezone? Yes, the date is based on your user timezone in Salesforce. For example, user A with Pacific time will see a date time field as "10/01/2021 18:27"; however, user B in Singapore time will see the same data as "1/11/2021 10:27 AM". The formula field calculation will only based on date; therefore, the result of aging between A and B will be one day different. For example, the below case is an open case, and today's date is 3rd Nov 2023.

User A


User B


Note: since this formula field is using the DATEVALUE() formula, if your users are also located in the daylight savings timezone, you need to enable "Improve DATEVALUE() accuracy for DST" under "Company Information" in the setup menu (check out this article); otherwise the calculation for users (with daylight saving timezone) related to processing times between 11:00 PM and 1:00 AM may wrong.


Another sample: 

The report was run on 1-Dec-2023 at 3:23 PM, SGT.

The same report is opened by different users with Pacific time with "Improve DATEVALUE() accuracy for DST" enabled. The current time for this user was 30-Nov-2023 at 11:23 PM PST.

Case 12311766, 12311380, and 12311379 were created on the same day as the current day, 30-Nov-2023, for users in the Pacific time, so those cases' ages (excluding weekends) are 0 days.

Time difference

The above case age calculation will ignore the time of case creation and time of case closed. The following formula will return the time in hours (while ignoring the date) -- result in the number field with 2 decimal points.

(TIMENOW() - TIMEVALUE(CreatedDate))/3600000


The above report is run on 30-Nov-2023 at 4:34 PM:
  • the row 4th from bottom: this is counted between 4:46 PM with 4:34 PM = 23.81
  • the row 3rd from bottom: this is counted between 3:56 PM with 4:34 PM = 0.64
So, this is clear that the dates are simply ignored.


Reference:




Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Page-level ad