Cell Format from General

D

David Biddulph

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

Next time, try to enter the data as 07:02, not as 0702.
 
J

John

It is because you have the times with out any delimiter so excel thinks you
are talking about days from 1/1/1900 so for 0702 you will have 02/12/1901
00:00:00 shown in the formula bar (ie 702 days from 1/1/1900).
I don't think you will be able to change this by formating the cell but
could fake it by putting =LEFT(A1,2) & ":" & RIGHT(A1,2) in a cell if you
have the 'time' you want in cell A1. Cell A1 would have to be formated as
text if you have any times starting with a zero.

hope this helps
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
S

S.C

I have several rows of data that were exported into a word document. I cut
and paste the data into a notepad document and save as text. I then import
the txt into a excel workbook.

My problem is that I have several columns of "times" (0702, 1340, 2250).
When I try to change the cell formatting too "time" it shows as 12 00 00 AM

Is there a way to format the cell and keep the actual time data?

Thanks in advance for any assistance.

Steve
 
S

S.C

The data I get I have no control over how it is exported to me, since it is
in a word document makes it a pain.

Thanks for all of the suggestions .... they work and have cut my time in
completing my task

Thanks again
 
Top