Cell Format from General

[David Biddulph]

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

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

 

[Guest]

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)

 

[KL]

=--TEXT(A1,"00\:00")
and format as time

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[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

 

[Guest]

=TIME(LEFT(A1,2),RIGHT(A1,2),)

 

[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