How to convert the string type of duration into minutes/seconds(numeric) for reporting purpose

Priyanka145
Tera Contributor

Hi,

I am having string type of fields which is calculating the difference between 2 date fields and storing into a string type of field.. So, the value appears in the format of days, hours, minutes, seconds.

(ex: 2 days, 10 hour, 45 minutes, 20 seconds)

But for reporting purpose it is not feasible to calculate .Hence , we want to convert into numeric value(minutes/seconds), etc to calculate properly.

(Ex: 6000) . 

So, in excel once report extracted will not face any issue.

Please guide me here, how to get that

 

@Ankur Bawiskar  Could you please guide me

1 ACCEPTED SOLUTION

Hi,

try this

(function executeRule(current, previous /*null when async*/ ) {

    //To display duration between RITM created time and RITM closed time
    var start = new GlideDateTime(current.opened_at); //opened time
    var end = new GlideDateTime(current.closed_at); //closed time
    var dc = new DurationCalculator();
    var sch = gs.getProperty('BusinessHoursSchedule');
    dc.setSchedule(sch); 
    var time = dc.calcScheduleDuration(start, end); 
    var durationMS = time * 1000;
  
    current.u_lifecycle_duration.setDateNumericValue(durationMS);

})(current, previous);

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

View solution in original post

6 REPLIES 6

Ankur Bawiskar
Tera Patron
Tera Patron

Hi,

that string field will be storing in this format -> 2 days, 10 hour, 45 minutes, 20 seconds

Why not use Duration field to hold the difference?

If not then you need one extra field which holds the duration possibly in seconds or milliseconds for reporting.

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Hi @Ankur Bawiskar , I tried to create a duration field and try to store the value in it. but it is not happening. The value is not being set to duration field. Please also let me know how can I convert duration which is in string to either seconds of milliseconds for reporting. Even I tried this way by dividing the duration/8640000 but it is showing as NAN. Please guide

Hi,

share complete script.

You can find difference between 2 dates and get the numeric value and store in duration field

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Hi @Ankur Bawiskar  , 

Please find below script

(function executeRule(current, previous /*null when async*/ ) {

    //To display duration between RITM created time and RITM closed time
    var start = new GlideDateTime(current.opened_at); //opened time
    var end = new GlideDateTime(current.closed_at); //closed time
    var dc = new DurationCalculator();
    var sch = gs.getProperty('BusinessHoursSchedule');
    dc.setSchedule(sch); 
    var time = dc.calcScheduleDuration(start, end); 
    var durationMS = time * 1000;
    var result = new GlideDuration(durationMS);
    var duration = result.getDisplayValue();
    current.setValue('u_lifecycle_duration', duration);
   

})(current, previous);