Forum Discussion

Copper Contributor

May 17, 2018

Solved

Seconds to MM DD hh:mm:ss

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...

Formulas & Functions

Need Help

Courtney Enniss
May 24, 2018
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")))))

Courtney Enniss

Copper Contributor

May 24, 2018

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