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?

Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

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?

This worked!!!! THANK YOU!!!!!

"CLR" wrote:


Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

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

On Thu, 6 Dec 2007 14:43:02 -0800, Suzanne
wrote:

This worked!!!! THANK YOU!!!!!

"CLR" wrote:


Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

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?

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

format cell: hh:mm

"Suzanne" wrote:

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?

On Thu, 6 Dec 2007 10:23:03 -0800, Suzanne
wrote:

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

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


"Suzanne" wrote in message ...
|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?

On Thursday, December 6, 2007 11:23:03 PM UTC+5, Suzanne wrote:
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?

bakwas; follow it; http://facebook.com/hatetharkipeople