Forum Discussion
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
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
=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
mtarler thank you, I'll give it a try!