Formatting a number string to "time"

[Norm Shea]

I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?

 

[Jonmo1]

Try

=TEXT(LEFT(A1,6)+0,"00\:00\:00")


However, the result is just a text string, not a real time.
If you want a real time, add +0 to the end and format the cell as a time.

 

[dispelthemyth]

Edit:

Better answer above

 

[Norm Shea]

Jonmo1,
Worked great! Thanks a bunch!

 

[Jonmo1]

Jonmo1,
Worked great! Thanks a bunch!

Glad to help, thanks for the feedback.

Edit:

Better answer above

No reason to hide your suggestion, the more options the better.
Your answer may help another user.

 

[Rick Rothstein]

No reason to hide your suggestion, the more options the better.
Your answer may help another user.

I had thought, if the user wanted a real date, that he could use this formula (one less function call)...

=0+TEXT(A1,"00\:00\:00\.000")

and then format the cell using h:mm:ss in order to get the display he wanted.

 

[Jonmo1]

=0+TEXT(A1,"00\:00\:00\.000")

Nice, I couldn't figure out what else to do with the extra 0s.
Didn't know they could be added as milliseconds..

 

[Rick Rothstein]

Nice, I couldn't figure out what else to do with the extra 0s.
Didn't know they could be added as milliseconds..

Since Excel (and VBA) times are stored as floating point numbers, the conversion routines from an entered time value to a real Excel (or VBA) time value can tolerate them... it is just the time display formats cannot show the fractional seconds back to you... they remain in the time value, but hidden from view. You can see this, sort of, as follows. First, format cells A1 and A2 with any Time format you want, then enter the following into the indicated cells...

A1: 1:23:45

A2: 1:23:45.123

Both A1 and A2 will display the same time value. Next, put these formulas in the indicate cells...

B1: =A1

B2: =A2

then, after entering those formula, format their cells as General. You will note the displayed decimal values are different even though their referenced time values are the same... that difference should be equivalent to 0.123 seconds... Excel (and VBA) can track decimal seconds, it is just that its time format cannot show them to you.

 

[dispelthemyth]

Glad to help, thanks for the feedback.


No reason to hide your suggestion, the more options the better.
Your answer may help another user.

Generally i would leave it, but i didnt try to change back to a real time as you did, so mine was far inferior