Forum Discussion

Courtney Enniss's avatar
Courtney Enniss
Copper Contributor
May 17, 2018

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.com/spreadsheets/d/1MPasewN64qomTNCgGEqa0QDqX1q6Zqc3OXElffZJam4
Formulas & Functions
Need Help
  • Courtney Enniss's avatar
    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's avatar
    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")))))

Resources