Converting seconds to dd:hh:mm:ss

Janine Neal

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

kirstie858

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

kirstie858

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

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