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

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

"S.C" wrote in message
...
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

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.C" wrote:

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

=--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/pr...A-9E6C73C09A36


"David Biddulph" <groups [at] biddulph.org.uk wrote in message ...
=TIME(INT(A1/100),MOD(A1,100),0)

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

"S.C" wrote in message
...
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

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

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


"S.C" wrote:

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

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



"S.C" wrote in message
...
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