NetSuite - Computation of Formula Fields of Time between Date Created and Incident Date/Time Excluding Non-business Days/Hours in a Case Saved Search

You can create a case saved search to compute the Date created less incident date/time and excluding non-business days/ hours, using formula fields.

Summarized the steps as below:

* Notes:
          Sample business hours: 9:00am to 5:00pm
          Non-working days: Saturdays and Sundays


Steps:

I. Create 4 Custom CRM fields.

The first two fields will get the Date created and Incident date
The last 2 will get the Time (in hours, decimal) the case was Created and Incident time.
 

1. Setup > Customization > CRM Fields > New
*This field will get the "Date" the Case record was CREATED
- Label = 111 Date Created
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = CASE WHEN TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24) when
to_char({createddate},'HH24') >= 17 then to_char(trunc({createddate}) + 17/24)  else to_char({createddate}) end ELSE to_char(trunc({createddate}) + 17/24) END
**sample Field ID: custevent42
 
2. Setup > Customization > CRM Fields > New
*This field will get the "Date" as per INCIDENT DATE.

- Label = 222  Incident Date
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = CASE WHEN TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24) when to_char({startdate},'HH24') >= 17
then to_char(trunc({startdate}) + 17/24)  else to_char({startdate}) end ELSE to_char(trunc({startdate}) + 17/24) END
**sample Field ID: custevent44
 
3. Setup > Customization > CRM Fields > New
*This field will get the "Time", expressed in Hours, the Case record was CREATED
- Label = 333 Time Created
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = (to_number(CASE WHEN TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24,'HH') when
to_char({createddate},'HH24') >= 17 then to_char(trunc({createddate}) + 17/24,'HH24')  else to_char({createddate},'HH24') end ELSE to_char(trunc({createddate}) + 17/24,'HH24') END) + (to_number(CASE WHEN
TO_CHAR({createddate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({createddate},'HH24') < 9 then to_char(trunc({createddate}) + 9/24,'MI') when to_char({createddate},'HH24') >= 17 then
to_char(trunc({createddate}) + 17/24,'MI')  else to_char({createddate},'MI') end ELSE to_char(trunc({createddate}) + 17/24,'MI') END) / 60))
**Field ID: custevent45
 
4. Setup > Customization > CRM Fields > New
*This field will get the "Time", expressed in Hours, of the "Incident date"
- Label = 444 Incident Time
- Type  = Free form text
- Store Value = False
- Applies to > Case = True
- Display > subtab = Main
- Validation & Defaulting:
 Formula = True
 Default Value = to_number(CASE WHEN TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24,'HH24') when
to_char({startdate},'HH24') >= 17 then to_char(trunc({startdate}) + 17/24,'HH24')  else to_char({startdate},'HH24') end ELSE to_char(trunc({startdate}) + 17/24,'HH24') END) + (to_number(CASE WHEN
TO_CHAR({startdate},'D') BETWEEN 2 AND 6 THEN CASE WHEN to_char({startdate},'HH24') < 9 then to_char(trunc({startdate}) + 9/24,'MI') when to_char({startdate},'HH24') >= 17 then to_char(trunc({startdate}) +
17/24,'MI')  else to_char({startdate},'MI') end ELSE to_char(trunc({startdate}) + 17/24,'MI') END) / 60)
**Field ID: custevent46
 
II. Create a Saved search.
1. Reports > Saved Searches > All saved searches > New > Case

2.  Search Title = enter the Search Title
- Criteria tab > specify any criteria
- Results tab:
 - Number
 - Date Created
 - Incident Date
 - 111 Date Created
 - 222  Incident Date
 - 333 Time Created
 - 444 Incident Time
 - Formula (Text) > Formula = case when to_date({custevent44}) - to_date({custevent42}) = 0 then to_number({custevent46}) - to_number({custevent45})  when to_date({custevent44}) - to_date({custevent42}) = 1 then to_number({custevent46})
+ to_number({custevent45}) when to_date({custevent44}) - to_date({custevent42}) > 1 then (18 - to_number({custevent46})) + (18 - to_number({custevent45})) + (((to_date({custevent44}) - to_date({custevent42}))
- 1) * 8) end

- Save and Run