adding a colon to military time

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

[CLR]

Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3

[Teethless mama]

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

format cell: hh:mm

[Ron Rosenfeld]

On Thu, 6 Dec 2007 10:23:03 -0800, Suzanne <Suz...@discussions.microsoft.com>
wrote:

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

[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

"Suzanne" <Suz...@discussions.microsoft.com> wrote in message news:C6583809-16BB-42B3...@microsoft.com...

[Suzanne]

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

[Gord Dibben]

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 <Suz...@discussions.microsoft.com>
wrote:

[seo.g...@gmail.com]

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