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!

[cag_onganer]

Dear all,

We are certainly watching these threads. I can assure you that there is an enhancement request for this feature. I will not be able to disclose more but it is an enhancement that will help out immensely for excel based reporting.

Regards,

Cag Onganer

[Victor Ruiz]

You can drop this formula into a column after export to convert the milliseconds to days and hours:  

=ROUNDDOWN(D6/86400,0)&" days "&ROUNDDOWN((D6-((ROUNDDOWN(D6/86400,0)*86400)))/3600,0)&" hrs"

make sure you change the field value...the field in the example above is D6

[Ryan Gladwyn-Na]

Added to the formula to show days, hours and minutes:

=ROUNDDOWN(E2/86400,0)&" days "&ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)&" hrs "&ROUNDDOWN((E2-(ROUNDDOWN((ROUNDDOWN(E2/86400,0)*86400)/3600,0)*3600)-(ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)*3600))/60,0)&" mins"

Make sure to change all occurrences of E2 to point to the cell containing your duration in seconds.

[ralphalberti]

Added to the formula to conditionally drop days and hours if there are none.

=

IF(E2>=86400,ROUNDDOWN(E2/86400,0)&" days "&ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)&" hrs "&ROUNDDOWN((E2-(ROUNDDOWN((ROUNDDOWN(E2/86400,0)*86400)/3600,0)*3600)-(ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)*3600))/60,0)&" mins",

IF(E2>=3600,ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)&" hrs "&ROUNDDOWN((E2-(ROUNDDOWN((ROUNDDOWN(E2/86400,0)*86400)/3600,0)*3600)-(ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)*3600))/60,0)&" mins",

IF(E2>=60,ROUNDDOWN((E2-(ROUNDDOWN((ROUNDDOWN(E2/86400,0)*86400)/3600,0)*3600)-(ROUNDDOWN((E2-(ROUNDDOWN(E2/86400,0)*86400))/3600,0)*3600))/60,0)&" mins",

""))

As Ryan said above, make sure to change all occurrences of E2 to point to the cell containing your duration in seconds.

[na93]

Hi Cag,

Has ServiceNow gotten any further with this?

Currently on Helsinki and it still extracts as seconds.