Notifications

1400 views

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 in 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 on 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 on the form header and select Export > Excel (This Record).

    Note that the Duration field is formatted in Seconds.

Resolution

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:2019-09-30 02:02:03
Published:2019-09-30