Converting seconds to dd:hh:mm:ss

Options

I am trying to import a report from Service Now that shows how long it took for a ticket to close. The duration field from Service now to excel export is in seconds. I need a field that would show the duration in days, hours, minutes. Service now has a formula for excel but I am having trouble converting it into Smartsheet:

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)"


Best Answer

  • kirstie858
    kirstie858 ✭✭✭✭
    Answer ✓
    Options

    Is this the desired output?


    =ROUND([Duration_Seconds]@row / 86400, 0) + " day(s) " + ROUND(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24, 0) + " hour(s) " + ROUND((([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24 - ROUNDDOWN(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24)) * 60, 0) + " minute(s) "

Answers

  • kirstie858
    kirstie858 ✭✭✭✭
    Answer ✓
    Options

    Is this the desired output?


    =ROUND([Duration_Seconds]@row / 86400, 0) + " day(s) " + ROUND(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24, 0) + " hour(s) " + ROUND((([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24 - ROUNDDOWN(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24)) * 60, 0) + " minute(s) "

  • Janine Neal
    Options

    Thank you!! I had to change it to all ROUNDDOWN:

    =ROUNDDOWN([Duration_Seconds]@row / 86400, 0) + " day(s) " + ROUNDDOWN(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24, 0) + " hour(s) " + ROUNDDOWN((([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24 - ROUNDDOWN(([Duration_Seconds]@row / 86400 - ROUNDDOWN([Duration_Seconds]@row / 86400, 0)) * 24)) * 60, 0) + " minute(s) "

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!