Convert poorly formed text dates into XL dates (Excel 97)

Topic

New Reply

I have several thousand rows of dates mostly all entered in “MMMM DD/YY” format. They are not stored as dates, but as text strings. I’ve tried the DATEVALUE function with no joy. Does anyone know of a way to convert these into “real” dates. I could do it in Excel 97 or Access 97, which is the ultimate destination of this data.

Reply | Quote

Replies

find – replace the “/” with “, 19” or “, 20” and they should convert themselves

Steve

Reply | Quote

Thank you Steve. I’ll try it. Am also having good luck with using the VBA DateValue function. I inserted a column to the right of my column of mal-formed dates and used: activecell.value = DateValue(activecell.offset(0,-1).value).

Reply | Quote

Had a similar situation with inconsistent user input…

I put the columns, I had multiple, through the old analog machine first.

Sorted the columns and looked at the extremes; that old chaff and wheat thing.

A quick look at the top and bottom and some quick Find & Replace saved a lot of headaches and time.

Good Luck,

Reply | Quote