adding a colon to military time

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?
 
I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?
Click to expand...

1. Is the 1700 an Excel time formatted as hhmm, or is it merely the number
1700?

2. Do you want the result to be recognized by Excel as a Time, or just as a
number?

3. To change the format of 1700 so it "looks like" 17:00 (but the stored value
is still 1700) then select the cell(s) and
Format/Number/Custom Type: 00\:00

4. To change it to a value recognized as a Time by Excel, you will need a
formula (see below). Then Format/Number/Custom Type: hh:mm

Formula with 1700 in A1: =INT(A1/100)/24+MOD(A1,100)/1440


--ron
 
You had a few replies giving you the right "image" of the time. If you require to calculate with the time, you need to make it
"real" Excel time: a fraction (of a day).

Is your time always 4 positions? ( i.e. no 745 for quarter to eight, but 0845)
If so, with the time in A1:

=TIME(INT(A1/100),MOD(A1,100),0)

Choose the appropriate format from the Format menu, or choose Format Custom, hh:mm

Post again in this thread if this doesn't answer your question

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I need to add a colon to military time. I need to change it from 1700 to
| 17:00. I want it to stay in military time. I don't want am or pm to show.
| I can't figure out how to do this on the worksheet. Anyone know how to do
| this?
 
Just as long as you are aware that this is not a real time.


Gord Dibben MS Excel MVP
 
Back
Top