Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formatting
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formatting
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 "Liz J" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formatting
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? "Liz J" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formatting
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 "Liz J" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time Formatting
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),) -- David Biddulph "dlw" wrote in message ... 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 "Liz J" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formatting hh:mm:ss | Excel Discussion (Misc queries) | |||
Formatting time for m:ss.00 | Excel Discussion (Misc queries) | |||
Time formatting | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |