SOLVED

Seconds to MM DD hh:mm:ss

Copper Contributor
Hey, I’m trying to convert seconds to MM DD hh:mm:ss but it seems to confuse months and days as a date rather than a period of time elapsed.

This is what I’m playing around with https://docs.google.com/spreadsheets/d/1MPasewN64qomTNCgGEqa0QDqX1q6Zqc3OXElffZJam4
1 Reply
best response confirmed by Courtney Enniss (Copper Contributor)
Solution
Thanks for the help..

I got this answer from a friend and it’s working well

=IF(I2<60,TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"),IF(I2<3600, TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), IF(I2<86400, TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), IF(I2<2592000,ROUNDDOWN(I2/86400,0) & IF(ROUNDDOWN(I2/86400,0)>1,"d ", "d ") & TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), ROUNDDOWN(I2/2592000,0) & IF(ROUNDDOWN(I2/2592000,0)>1, "M ", "M ") & ROUNDDOWN(MOD(I2,2592000)/86400,0) & IF(ROUNDDOWN(I2/86400,0)>1,"d ", "d ") & TEXT(MOD(I2,2592000)/86400,"hh:mm:ss")))))
1 best response

Accepted Solutions
best response confirmed by Courtney Enniss (Copper Contributor)
Solution
Thanks for the help..

I got this answer from a friend and it’s working well

=IF(I2<60,TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"),IF(I2<3600, TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), IF(I2<86400, TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), IF(I2<2592000,ROUNDDOWN(I2/86400,0) & IF(ROUNDDOWN(I2/86400,0)>1,"d ", "d ") & TEXT(MOD(I2,2592000)/86400,"hh:mm:ss"), ROUNDDOWN(I2/2592000,0) & IF(ROUNDDOWN(I2/2592000,0)>1, "M ", "M ") & ROUNDDOWN(MOD(I2,2592000)/86400,0) & IF(ROUNDDOWN(I2/86400,0)>1,"d ", "d ") & TEXT(MOD(I2,2592000)/86400,"hh:mm:ss")))))

View solution in original post