How to display duration field value in a proper format in an excel report

[Johannes Mweli]

Hi ServiceNow Community Developers,

I have a very simple report running on the incident table and has a duration field in it. When I look at the report in the ServiceNow platform it looks good, the duration field value is displaying as expected with the proper format. However when I download it into spreadsheet the value of "3 Days 21 Hours 1 Minute" for instance that I see in the duration field inside the ServiceNow platform gets displayed as "334909" and I am not show why.  

Would you guys please advise as to how can I overcome this issue when downloading a report that has duration field into a spreadsheet. Basically in the spreadsheet I would like to see the same value as I see in the report inside ServiceNow.

Thanks,

Johannes

[Jeff Currier]

I seem to be off by 49 seconds, but I think this is what you are seeing

3 days is 259200 seconds

21 hours is 75600 seconds

1 minute is 60 seconds

Add them together you get a number very close to 334909.  So you are seeing duration in seconds in your spreadsheet.  You can create a formula in Excel to covert this to days, hours and minutes.

[Jeff Currier]

Just realized I never finished explaining the why.  The why is because of the data type.

The duration field type is the length of time, which is stored in the database as an integer number of milliseconds, but appears in days, hours, minutes, and seconds.

So when you export, you get it in the format in which is is stored, not displayed as the export function doesn't know the specific display format used on that form.

[Johannes Mweli]

Thanks JeffCsn - I am still looking for the formula to use however I fully understand what is going on here, you have explained things pretty well

[Jeff Currier]

I think you have two options.  

1) Apply a formula to the duration column in Excel.  If you calculate a new column based on the exported duration you will get the format you want

=TEXT(A5/(24*60*60),"dd \d\a\y\s hh \h\o\u\r\s mm.ss \m\i\n")
or
=TEXT(A5/(24*60*60),"d \d\a\y\s h \h\o\u\r\s m.s \m\i\n")

Just change A5 to the correct column and row and copy for all rows

Or

2. Calculate a new string field for your table and update this field with the string value of the duration.  Then include that field in your export.

I like option 1, but might depend on your use case.

And a minor point, 3 Days 21 Hours 1 Minute is rounding down.  The duration in your use case is specifically: 3 Days 21 Hours 1.49 Minutes