Forum Discussion

Cullen1408's avatar
Cullen1408
Copper Contributor
Jul 17, 2023

Convert Imported Time to 00:00:00

Hello,

 

I am trying to set times imported into Excel as 151521 and change it to 15:15:21.  When I use the Format Time feature it resets all of my data to 0:00:00.

 

Thank you,

Aimee

excel
office 365

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 18, 2023
    so the number 151521 is a whole number so by using "format time" excel thinks that is a date-time format and that format is based on integer number of days and fraction of a day would be the time and since that number has no decimal places it is a whole number of days and hence the time is 00:00:00 on that day. Try using something like
    =TEXT(A1,"00\:00\:00")
    which will output a TEXT string in the form you want or
    =TIMEVALUE(TEXT(A1,"00\:00\:00"))
    will then return an actual date-time format and hence you need to set the cell to 'Time' format

Resources