Time Format?

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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.
 
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)
 
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
 
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!
 
Yep, to make it longer <g>

--

Regards,

Peo Sjoblom

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

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