How to calculate business duration in days

[sath]

Hi,

We need to calculate business duration in business days on each story state. 

Used the below script:

        var busDuration = calcDurationSchedule(gr.start, gr.end);
        var busDurationInMilliseconds = busDuration.getNumericValue();
        var busDurationInHours = busDurationInMilliseconds / (1000 * 60 * 60);
        var businessDays = Math.floor(busDurationInHours / 8);   //8 business hours in a day
        var businessDuration = new GlideDuration(businessDays * (24 * 60 * 60 * 1000));

        function calcDurationSchedule(start, end) {
            var user = new GlideRecord('sys_user');
            user.get(gs.getUserID());
            var sched = new GlideSchedule('887eecae8h8h0b356077e1dfa71da345', user.time_zone);
            return (sched.duration(start.getGlideObject(), end.getGlideObject()));
        }
        gr.business_duration = businessDuration;

This is working, but only if the time between each story state is more than a business day, if it's within minutes or hours, then it returns 0.

I have used below script too and it returns the value in business days, but its a legacy feature and uses default SLA calendar whereas we want to use our defined schedule.

var businessduration = gs.calDateDiff(gr.start.getDisplayValue(), gr.end.getDisplayValue(), false);
gr.business_duration = businessduration;

Please assist if there's any way of getting business duration in business days using our defined schedule.

[_ukasz Rybicki]

---

Problem Name: Calculating Business Duration in Days 🕒📅

---

General Solution Proposal:

To accurately calculate business duration in days within ServiceNow, especially when excluding non-business hours and specific states like 'On Hold', it's recommended to utilize the SLA engine with defined schedules and pause conditions. This approach ensures that only the active business time is considered, providing a precise measurement of the duration.

---

Detailed Step-by-Step Solution:

1. Define a Business Schedule:

   • Navigate to System Scheduler > Schedules.

   • Create a new schedule (e.g., "Business Hours") specifying the working days and hours (e.g., Monday to Friday, 9 AM to 5 PM).

   • Save the schedule.

2. Create an SLA Definition:

   • Go to Service Level Management > SLA Definitions.

   • Click on New to create a new SLA.

   • Fill in the necessary details:

     • Name: e.g., "Incident Resolution Time"

     • Table: Select the relevant table (e.g., Incident).

     • Duration: Set the target duration (e.g., 3 days).

     • Schedule: Select the previously created "Business Hours" schedule.

   • Under the Conditions tab:

     • Start Condition: e.g., [State] [is] [New]

     • Pause Condition: e.g., [State] [is] [On Hold]

     • Stop Condition: e.g., [State] [is] [Resolved]

   • Save the SLA definition.

3. Attach SLA to Records:

   • Ensure that the SLA is attached to the relevant records either through SLA Conditions or by scripting, depending on your organization's configuration.

4. View Business Duration:

   • The SLA will calculate the business duration based on the defined schedule and conditions.

   • You can view the business duration in the related SLA records or by adding the Business Duration field to your form or list views.

---

Example Solution:

For the Incident table, to measure the time taken to resolve an incident excluding 'On Hold' periods:

• Schedule: Monday to Friday, 9 AM to 5 PM.

• SLA Definition:

  • Name: "Incident Resolution Time"

  • Table: Incident

  • Duration: 3 days

  • Schedule: Business Hours

  • Start Condition: State is New

  • Pause Condition: State is On Hold

  • Stop Condition: State is Resolved

This setup ensures that the SLA tracks only the active business hours, pausing during 'On Hold' states, and provides an accurate business duration.

---

Testing the Solution:

1. Create a Test Incident:

   • Set the state to 'New' and note the time.

2. Change State to 'On Hold':

   • After some time, change the state to 'On Hold' and note the time.

3. Change State to 'Resolved':

   • After some time, change the state to 'Resolved' and note the time.

4. Verify Business Duration:

   • Navigate to the related SLA record for the incident.

   • Check the Business Duration field to ensure it reflects only the active business time, excluding the 'On Hold' period.

---

Sources:

1. ServiceNow Community - Business Duration Calculation:

   • Business Duration Calculation

   • Discussion on calculating business duration excluding 'On Hold' states using SLA definitions.

2. ServiceNow Community - Calculate Business Days:

   • Calculate Business Days

   • Insights on calculating business days using schedules and SLA definitions.

3. ServiceNow Community - Metric Definition for Business Duration:

   • Metric Definition for Business Duration

   • Guidance on using metric definitions and schedules to calculate business duration.

---

If you need further assistance or have specific requirements, feel free to ask! 😊

---

[sath]

Hi,

This is on stories and we don't want to use SLAs. Thank you.

[_ukasz Rybicki]

---

🛠️ Problem Name: Calculating Business Duration Without SLA

---

🔍 General Solution Proposal:

To calculate business duration in days for stories without using SLAs, implement a Business Rule that utilizes the GlideSchedule API. This approach calculates the duration between two date fields (e.g., opened_at and closed_at) based on a defined business schedule, excluding non-working hours and holidays.

---

📝 Detailed Step-by-Step Solution:

1. Identify the Business Schedule:

   • Navigate to System Definition > Schedules.

   • Locate the appropriate schedule (e.g., "8-5 weekdays") and note its sys_id.

2. Create a Business Rule:

   • Go to System Definition > Business Rules.

   • Click New to create a new Business Rule with the following settings:

     • Name: Calculate Business Duration

     • Table: Your custom table (e.g., u_story)

     • When: Before

     • Insert: Checked

     • Update: Checked

     • Advanced: Checked

3. Add the Script:

   • In the Advanced section, input the following script:

     (function executeRule(current, previous /*null when async*/) {
         // Replace with your schedule's sys_id
         var schedule = new GlideSchedule('your_schedule_sys_id');
     
         // Ensure both dates are available
         if (current.opened_at && current.closed_at) {
             var start = new GlideDateTime(current.opened_at);
             var end = new GlideDateTime(current.closed_at);
     
             // Calculate business duration in milliseconds
             var durationMs = schedule.duration(start, end).getNumericValue();
     
             // Convert milliseconds to days
             var durationDays = durationMs / (1000 * 60 * 60 * 24);
     
             // Set the calculated duration (rounded to 2 decimal places)
             current.u_business_duration = parseFloat(durationDays.toFixed(2));
         }
     })(current, previous);

   • Replace 'your_schedule_sys_id' with the actual sys_id of your schedule.

   • Ensure that the field u_business_duration exists on your table to store the calculated duration.

4. Save the Business Rule:

   • Click Submit to save the Business Rule.

---

✅ Example Use Case:

Scenario: On the u_story table, calculate the business duration in days between opened_at and closed_at without using SLAs.

Implementation:

• Business Rule named "Calculate Business Duration" on the u_story table.

• Uses the organization's standard 8-5 weekday schedule.

• Stores the result in the u_business_duration field.

---

🧪 Testing the Solution:

1. Create Test Records:

   • Insert records into the u_story table with known opened_at and closed_at values spanning multiple business days.

2. Verify Calculations:

   • After saving, check the u_business_duration field to ensure it reflects the correct number of business days.

3. Edge Cases:

   • Test records that start or end outside business hours to confirm accurate calculations.

---

📚 Sources:

1. ServiceNow Community - Business duration calculation through background script:

   • Link

   • Discusses calculating business duration using GlideSchedule and background scripts.

2. ServiceNow Community - Business Duration Calculation for PA:

   • Link

   • Provides insights on using GlideSchedule for business duration calculations in Performance Analytics.

---

If this solution works for you, please mark it as the accepted answer. Let me know if you need further assistance! 😊

---

[DrewW]

Are the Start and End date fields Dates or Date/Times?  

Also Math.Floor will return the lowest Integer for the value.  So if you feed it 0.99999 it will return 0.  So it looks like line 4 is going to reduce anything less than 8 hours to 0 days.

Why not just use your scheduled to calculate the duration and then put that value directly into the duration field?  You can then just use the attribute max_unit=hours and let the user figure our the days from the total hours.

You also may want to just consider a read only string/float field to put the value into.