duration fields in export to excel

[auntyem]

I've already found this in the wiki:

5.1 Excel
Date, Date-Time, and Time fields are all exported as their display values, displayed using a custom format instead of the system date format. Duration fields, however, export as the value stored in the database, which is an integer value of seconds.

The last part about duration fields in seconds is REALLY undesirable; customers are complaining that the report is useless. Is there a way to configure this to be something friendly like hours?

Thanks!

[Ryan Ramos]

I come up with same problem and i did this.

created custom field for duration_text, make it read only and add this script on calculation value.

var dur = new GlideDuration();
dur.setValue(current.duration);
return dur.getDisplayValue();

Output: 

5 Days 20 Hours 59 Minutes

OOB field shows 337358

then replace the field to this custom field to export instead of the OOB duration field.

[Michelle11]

I wonder if anyone has tried another way to solve this case other than using a script?

[Mitch7]

Not sure if people still struggle with this, but...

I was exploring options with this and found a bit of a workaround. It'll create a column that's easily human readable as I used a String field to capture the Display Value of the duration I calculated.

In my example we have two Date fields on the incident form and there's a Business Rule that calculates the duration between the two. If that duration is assigned to a field of Duration type then it'll export as seconds as mentioned.
However if the field is a String type instead of Duration and you assign it the Display Value then it'll store it as a String and when exported it'll display as Days Hours Minutes.

When assigning it set it like: current.stringfield = duration.getDisplayValue(); (stringfield being the name of your field of course).

This will spare having to do any work on the Excel sheet after it's been exported. I actually have two fields, one that stores it in total seconds so we can perform math calculations on it and another that stores it as a string for humans to read it easily.

Hope this helps.

[Michelle11]

Could you please send me a step by step of how you did it ?

[Akshay Bhaskar]

We finally have a solution to this issue and i.e. to add a sys_property - 'glide.export.excel.duration.readable_string' with Type - true/false and set the value to TRUE. 

More details can be found here - https://docs.servicenow.com/bundle/washingtondc-integrate-applications/page/administer/export-sets/r...

Regards,
Akshay