Time Formatting

L

Liz J

I'm looking at time data that is in army-time, but it does not have the
colons. So, the entire row has figures like this 2204, instead of 22:04.

Could you please help me to format this properly?

Thank you!
 
D

dlw

in a helper column:
=LEFT(A1,2)&":"&RIGHT(A1,2)
note, this turns it into a text so you can't do math on it anymore, and
times before 10 must be formatted 0904, etc
 
A

atropos557

When you right-click on one of the time cells and go to "format cells" what
is the category highlighted on the "number" tab? Does it say "time"? If not,
that may be your problem. Select "time" and then click on the correct option.
If not what happens if you change it to something else and click okay and
then go back and change it back to "time". Do the options then show you army
time with the colons?
 
P

Pete_UK

If you want those to be converted to proper Excel times, you can do this in
a helper column:

=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00")

Then format the cell as a time in the style you want.

Hope this helps.

Pete
 
D

David Biddulph

You can address dlw's 2 concerns by changing that formula from
=LEFT(A1,2)&":"&RIGHT(A1,2)
to
=--(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)) and formatting appropriately as
time,
or by using
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)
 
Top