Time Format?

[Ken]

Excel 2000 ... I have a Column of Data (pasted in from
Access) where each cell contains a 4 character numeric
value representing Military Time (numbers on left below)

0675 = 6:30 am
1125 = 11:15 am
1550 = 3:30 pm
2350 = 11:30 pm

How can I chg the 4 character numeric number to time as
indicated on right? I tried "Format Cells" ... then
selected Time", but values remained same ... Thanks ... Kha

 

[JE McGimpsey]

One way:

First, I assume that your first example is a typo and should be 6:45 am.

Since XL stores times as fractional days, hours should be divided by 24

B1: =(A1/100)/24

or

B1: =A1/2400

Format B1 as a time.

 

[Norman Harker]

Hi Ken!

See:

Chip Pearson
http://www.cpearson.com/excel/DateTimeEntry.htm

Or you can use formulas to create Excel times in a helper column

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

 

[Peo Sjoblom]

You have a typo there, 0675 should be 6:45 not 6:30

try

=INT(A1/100)/24+MOD(A1,100)/100/24

format as hh:mm

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi
one way: try the following formula
=TIME(LEFT(A1,2),--RIGHT(A1,2)/100*60,0)
and format the resulting cell with a time format

 

[Norman Harker]

Hi Ken!

Reading your examples more closely:

Try:
=TIME(INT(A1/100),MOD(A1,100)/100*60,0)

It seems that your using hours and decimal hours to two decimal places
and omitting the decimal point. That threw me!

 

[JE McGimpsey]

Any reason to use three functions and three operations instead of

=A1/2400

?? <g>

 

[Peo Sjoblom]

Yep, to make it longer <g>

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Norman Harker]

Hi JE!

Stupidity! Plus too late! Plus totally thrown by the data entry
format. But primarily, stupidity.