Date not recognized in Excell 2000

C

ChrisB

Hi -

I am trying to generate some summaries of large amounts of data in a
range using the frequency formula. In order to do this I need to
extract all the date time stamps from several thousand rows of data
and put them into new cells. Then I will use the frequency formula to
get what I need.

I ran into a problem when extracting the date from original text
string. The date/time is parsed out of the following string using a
mid formula.

DEBUG SMTP RCVD: 220 smtp.dc.gov ESMTP server ready Sat, 24 Jul 2004
00:47:59 -0400
=mid(A1,58,20) --> 24 Jul 2004 00:47:59

The forumla values are then copied and pasted into a new set of cells
to have the actual data. The destination cells are formatted as
date/time cells but the value is not recognized as such. If I move to
a cell, enter F2 and the leave the cell Excel then recognizes it as a
date.

This is great but I have several thousand values that need be
recognized as dates.

Is there a way to accomplish this same thing all at once? Why isn't
Excel recognizing the value as a date until the cell becomes active?
This is really frustrating. Any help you can provide will be
appreciated.

Thanks,
Chris
 
M

Myrna Larson

Try using the formula =DATEVALUE(MID(A1,58,20). Convert formulas to values via
Paste Special if needed.
 
R

Ron Rosenfeld

Hi -

I am trying to generate some summaries of large amounts of data in a
range using the frequency formula. In order to do this I need to
extract all the date time stamps from several thousand rows of data
and put them into new cells. Then I will use the frequency formula to
get what I need.

I ran into a problem when extracting the date from original text
string. The date/time is parsed out of the following string using a
mid formula.

DEBUG SMTP RCVD: 220 smtp.dc.gov ESMTP server ready Sat, 24 Jul 2004
00:47:59 -0400
=mid(A1,58,20) --> 24 Jul 2004 00:47:59

The forumla values are then copied and pasted into a new set of cells
to have the actual data. The destination cells are formatted as
date/time cells but the value is not recognized as such. If I move to
a cell, enter F2 and the leave the cell Excel then recognizes it as a
date.

This is great but I have several thousand values that need be
recognized as dates.

Is there a way to accomplish this same thing all at once? Why isn't
Excel recognizing the value as a date until the cell becomes active?
This is really frustrating. Any help you can provide will be
appreciated.

Thanks,
Chris
Click to expand...

Myrna's solution will give you a value that is equal to only the date. If you
require the time as well as the date, you can use the following formula:

=--MID(A1,58,20)

The leading double unary will convert the string into the equivalent date+time.

Format it as you wish.

--ron
 
M

Myrna Larson

Good point, Ron. Thanks for the addition.

Myrna's solution will give you a value that is equal to only the date. If you
require the time as well as the date, you can use the following formula:

=--MID(A1,58,20)

The leading double unary will convert the string into the equivalent date+time.

Format it as you wish.

--ron
Click to expand...
 
Top