Case Saved Search Formula > Difference between Incident Date/Time & Escalated Date/Time In Minutes

Since we are not able to pull up the value of Incident Date/Time less Escalated Date/Time from the System Notes prior to converting the Date/Time as Minutes, a custom CRM field must be created & applied to the Case record indicating the time of when the case record was escalated. The Incident Date/Time is already the {startdate}.

A. Create a custom CRM field

The purpose of this is to be able to easily locate the Date/Time of when the case has been escalated.

1. Navigate to Customization > Lists, Records, & Fields > CRM Fields > New.
2. Label as desired.
3. Type = Date/Time.
4. Store Value = T.
5. Applies To = Case. 
6. Display to Subtab = Main.
7. Display Type = Inline Text.
8. Click Save.

Sample internal ID to this is custevent639 which I created in a demo account for testing purposes.

B. Create a workflow

The purpose of this workflow is to be able to set a field value to the custevent1 automatically once the Case Status = Escalated from any previous Case Status (e.g. Open, In Progress, etc.). See the following screenshots:

C. Create/Edit the Saved Search Formula

Enter the following in the Results tab:

a. Number
b. Formula (Date/Time)
Formula = {startdate}
Custom Label = Incident Date/Time

• this refers to the Incident Date/Time from the case record

c. Formula (Date/Time)
Formula = {custevent1}
Custom Label = Escalated Date/Time

• this refers to the custom CRM field we have created which in my testing I labeled as Escalated Date/Time

d. Formula (Text)
Formula = {custevent1}-{startdate}
Custom Label = Difference Date/Time

• note that in the screenshot of the Saved Search Results, it shows as the number of days, HH:MI:SS (e.g. 2 02:25:00)

e. Formula (Numeric)
Formula = regexp_substr ({custevent639}-{startdate}, '^[^ ]+ ')
Custom Label = Days

• this is a breakdown of the Days from step d.

f. Formula (Text)
Formula = substr({custevent1}-{startdate},instr({custevent639}-{startdate},' ')+1)
Custom Label = Time

• this is a breakdown of the Time from step d.

g. Formula (Text)
Formula = regexp_substr(substr({custevent1}-{startdate},instr({custevent639}-{startdate},' ')+1), '^[^:]*')
Custom Label = Hours

• this is a breakdown of the Hours from step f.

h. Formula (Text)
Formula = replace(regexp_substr(substr({custevent1}-{startdate},instr({custevent639}-{startdate},' ')+1), ':[^:]*:'),':','')
Custom Label = Minutes

• this is a breakdown of the Minutes from step f.

i. Formula (Text)
Formula = replace(regexp_substr(substr({custevent1}-{startdate},instr({custevent639}-{startdate},' ')+1), ':[^:]*$'),':','')
Custom Label = Seconds

• this is a breakdown of the Seconds from f.

j. Formula (Numeric)
Function = Round to Hundredths
Formula = (to_number(Regexp_substr ({custevent1}-{startdate}, '^[^ ]+ '))*24*60)+(to_number(regexp_substr(substr({custevent1}-{startdate},instr({custevent1}-{startdate},' ')+1), '^[^:]*')*60)) + (to_number(replace(regexp_substr(substr({custevent1}-{startdate},instr({custevent1}-{startdate},' ')+1), ':[^:]*:'),':','')))+ (to_number(replace(regexp_substr(substr({custevent1}-{startdate},instr({custevent1}-{startdate},' ')+1), ':[^:]*$'),':',''))/60)
Custom Label = Time in Minutes

• the value of g+h+i converted to Minutes

k. Click Save & Run.