How to calculate business elapsed time ?

prash4 · ‎08-01-2023

Hi Community!

I am struggling to calculate business elapsed time in Taksa.

Requirement: some records are not updated with stop time, and we are trying to calculate business elapsed duration with start time and adding that time to stop time.

can someone please help me with the script. this is really important to me.

Tushar · ‎08-01-2023

@prash4

please run this fix script -

(function() {
  var taskGR = new GlideRecord('task');
  taskGR.addNullQuery('stop');
  taskGR.addQuery('task_sla.state', 'false');
  taskGR.addQuery('stage', 'completed');
  taskGR.query();

  while (taskGR.next()) {
    var start = new GlideDateTime(taskGR.getValue('start'));
    var elapsedTime = new GlideDuration(taskGR.getValue('business_stc'));

    if (!start.nil() && !elapsedTime.nil()) {
      var stop = new GlideDateTime(start);
      stop.add(elapsedTime);
      taskGR.setValue('stop', stop);
      taskGR.update();
    }
  }
})();

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

View solution in original post

Tushar · ‎08-01-2023

Hi @prash4

Please try to use below sample script -

function calculateBusinessElapsedDuration(taskRecord) {
  var startDateTime = new GlideDateTime(taskRecord.start_date_time); // Assuming start_date_time is the field name for the start time
  var stopDateTime = new GlideDateTime(taskRecord.stop_date_time);   // Assuming stop_date_time is the field name for the stop time
  
  // Check if the stop time is empty, and if so, use the current date and time as the stop time
  if (taskRecord.stop_date_time.nil()) {
    stopDateTime = new GlideDateTime(); // Current date and time
  }
  
  // Calculate the duration in milliseconds
  var durationInMilliseconds = stopDateTime.getNumericValue() - startDateTime.getNumericValue();
  
  // Calculate the business elapsed duration in hours, excluding weekends and holidays
  var businessElapsedDuration = gs.dateDiff(startDateTime.getDisplayValue(), stopDateTime.getDisplayValue(), true);
  
  // If the duration is negative (stop time is before start time), set it to zero
  if (businessElapsedDuration < 0) {
    businessElapsedDuration = 0;
  }
  
  return businessElapsedDuration;
}

// Example usage:
var taskId = 'replace_with_task_sys_id'; // Replace with the Sys ID of the task record you want to calculate the business elapsed duration for
var taskGR = new GlideRecord('task');
if (taskGR.get(taskId)) {
  var elapsedDuration = calculateBusinessElapsedDuration(taskGR);
  gs.info('Business Elapsed Duration (hours): ' + elapsedDuration);
}

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

prash4 · ‎08-01-2023

Hi @Tushar

Thank you for the update, appreciate your help on it.

Ex Scenario:

Business elapsed time: 115Days 14Hours 28Minutes 38Seconds

start time: 01/12/2022 09:17:25: AM

Stop time: empty.

we need to calcuate business elapsed time from start time to 115days and resulted date and time.
need to update in stop time.

Appreciate your quick response to it.

Tushar · ‎08-01-2023

You can use below Business Rule script to calculate and set the stop time for Task records when the stop time is not provided:

(function executeRule(current, previous /*optional*/) {
  // Check if stop time is not provided (empty or null)
  if (!current.stop) {
    var start = new GlideDateTime(current.start);
    var end = new GlideDateTime();
    // Calculate the elapsed time
    var elapsedTime = end.subtract(start);
    // Add the elapsed time to the start time to set the stop time
    current.stop = start.add(elapsedTime);
  }
})(current, previous);

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

prash4 · ‎08-01-2023

HI @Tushar

Here the data is old and there are 1030 records data where stoptime is not updated though task_sla state is false and stage is completed.

we are trying to write a fix script to update the stop time based on business elapsed time. to fix the issue.