Time calculations help

[Lucky1]

Hi all,

I have created a field on a catalog task table, "Total worked time" which is of Duration field type.

So, once the catalog task state is changed from Open to In Progress and until Closed complete, I want to calculate the time duration worked on it and auto-populate in that field once the task is closed complete.

Please note: The calculated time should not consider the pending state time duration.

Please guide.

Regards,

Lucky

[Rahul Kumar17]

Hi,

To calculate the time duration worked on a catalog task and auto-populate it in the "Total worked time" field, excluding the pending state time, you can use a combination of business rules and script calculations in ServiceNow. Here's how you can approach this:

1. Create a Business Rule:

   • Create a business rule on the catalog task table (e.g., sc_task) to trigger when the state changes.
   • Set the condition for the business rule to trigger when the state changes from "In Progress" to "Closed Complete".

2. Implement the Business Rule Script:

   • In the business rule script, you can calculate the time duration worked on the catalog task.
   • Use the GlideDateTime API to retrieve the start time (when the state changed to "In Progress") and end time (when the state changed to "Closed Complete").
   • Calculate the duration between the start and end time, excluding any pending state time.
   • Set the calculated duration value in the "Total worked time" field.

Here's an example of the business rule script:

(function() {
  // Get the current catalog task record
  var currentTask = current;

  // Check if the state changed from 'In Progress' to 'Closed Complete'
  if (currentTask.state == 3 && previous.state == 2) {
    // Calculate the time duration worked
    var startTime = currentTask.sys_created_on.getGlideObject();
    var endTime = currentTask.sys_updated_on.getGlideObject();
    var duration = new GlideDuration(endTime.subtract(startTime));

    // Exclude any pending state time
    if (currentTask.active.changes() && currentTask.active == false) {
      var pendingTime = currentTask.sys_updated_on.getGlideObject();
      var stateChangeTime = previous.sys_updated_on.getGlideObject();
      var pendingDuration = new GlideDuration(pendingTime.subtract(stateChangeTime));
      duration.subtract(pendingDuration);
    }

    // Set the calculated duration in the 'Total worked time' field
    currentTask.total_worked_time = duration.getDisplayValue();
  }
})();

Thanks,

Rahul Kumar

[Riya Verma · Accepted solution]

Hi @Lucky1 ,

Hope you are doing great.

1. Create a new Business Rule with the appropriate conditions to trigger when the task state is changed from Open to In Progress.

(function executeRule(current, previous /*null when async*/) {
    // Check if the task state has changed from Open to In Progress
    if (current.state == 2 && previous.state == 1) {
        // Set the current date and time as the start time of the task
        current.work_start = new GlideDateTime();
    }
})(current, previous);

Create a Workflow:.

• Create a new workflow or modify an existing one to include the following stages:
  • Stage 1: In Progress
  • Stage 2: Pending (optional if you have a specific pending state)
  • Stage 3: Closed Complete
• In the workflow, add a script to calculate the time duration worked and populate the "Total worked time" field when the task transitions from In Progress to Closed Complete.

// Check if the task is transitioning from In Progress to Closed Complete
if (current.state == 3 && previous.state == 2) {
    // Calculate the duration worked by subtracting the work start time from the current date and time
    var workStart = new GlideDateTime(current.work_start);
    var duration = new GlideDuration(workStart.getNumericValue(), new GlideDateTime().getNumericValue());

    // Set the calculated duration as the value of the "Total worked time" field
    current.total_worked_time = duration.getDisplayValue();
}