Notifications

277 views

Duration fields are not formatted in the export to Excel functionality and display as integer values in seconds

Description


The export to Excel functionality does not format Duration fields as <Days, Hours, Minutes>. Instead, they are shown as an integer in Seconds. This affects calculated duration type values stored on the Case Timeline, like Time to Alert, Time to Acknowledge, Time to Resolve, and Time to Respond.

Steps to reproduce


  1. Open a task_sla record.

  2. Right-click in the form header and select Export > PDF.

    Note that the Duration field (the Actual elapsed time column) is formatted as Days, Hours, Minutes (e.g. 838 Days 19 Hours 46 Minutes). This is the expected behavior.

  3. Right-click in the form header and select Export > Excel (This Record).

    Note that the Duration field is formatted in Seconds.

 

Workarounds


Use one of the following workarounds:

Workaround 1

  1. Create a new column in the Excel file.

  2. Apply the following formula:

    =IF(E2<60,TEXT(MOD(E2,2592000)/86400,"hh:mm:ss"),IF(E2<3600, TEXT(MOD(E2,2592000)/86400,"hh:mm:ss"), IF(E2<86400, TEXT(MOD(E2,2592000)/86400,"hh:mm:ss"), IF(E2<2592000,ROUNDDOWN(E2/86400,0) & IF(ROUNDDOWN(E2/86400,0)>1," days ", " day ") & TEXT(MOD(E2,2592000)/86400,"hh:mm:ss"), ROUNDDOWN(E2/2592000,0) & IF(ROUNDDOWN(E2/2592000,0)>1, " months ", " month ") & ROUNDDOWN(MOD(E2,2592000)/86400,0) & IF(ROUNDDOWN(E2/86400,0)>1," days ", " day ") & TEXT(MOD(E2,2592000)/86400,"hh:mm:ss"))))) where E2 is the cell with the duration value
  3. Drag the cell down in that column to format the rest of column E values.

Workaround 2

Create two new columns and reference the integer column in your formula.

  • For the first column, create Time in Decimal column L that contains the following formula

    =K2/60/1440

  • Create a second column M called Time in Duration that contains the following formula:

    =INT(columnrowwithdecimalvalue) & " day(s) " & TEXT(columnrowwithdecimalvalue,"hh") & " hour(s) " & TEXT(columnrowwithdecimalvalue,"mm:ss") & " minute(s)" 

    ... more specifically =INT(L2) & " day(s) " & TEXT(L2,"hh") & " hour(s) " & TEXT(L2,"mm:ss") & " minute(s)"

    Note the example in the following figure.

In the example:

  • 5395098 is the number of seconds.

  • 5395098/60/1440 = 62.44326389 (1440 is derived from 24 hours in a day, and 60 minutes per hour, thus 24*60=1440). Divide to get minutes, then divide that by the number of minutes in a day to get a decimal value, = 62.44326389.

    The result of pasting the formulas in Excel: 62 day(s) 10 hour(s) 38:18 minute(s)

Article Information

Last Updated:2018-08-08 22:58:57
Published:2018-08-09