adding a colon to military time

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?
 
R

Ron Rosenfeld

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?

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
 
N

Niek Otten

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?
 
G

Gord Dibben

Just as long as you are aware that this is not a real time.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top