Text to Date

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

I have been given a database dump (thousands of rows) that put the dates in a
text format like this:

Aug/11/2009

Is there any way to put this into a date format? Even if it means creating a
new field breaking it up and doing some extra steps.

Thanks.
 
Using the menu commnd Text to Columns worked for me.

Select the range of "dates".
Goto the menu Data>Text to Columns
Click Next twice
In Step 3 of the wizard select Date and from the drop down select MDY
Click Finish

Then format in the date style of your choice.
 
You can also select all the dates, click Edit/Replace on the menu bar, put a
"/" (without the quote marks) in the "Find what" field and ", " (comma
space, again, without the quote marks) in the "Replace with" field and
finish off by clicking the "Replace All" button.
 
Correct. It is not. I get the pesky, #Value!. Perhaps ISERROR would work,
but it isn't either.
 
Hi

Did you not try Biff's suggestion of
Data>text to Columns>Next>Next>Date>M/D/Y

That works perfectly and is the easiest way to go IMO.
 
The supplied formula works perfectly with the data you provided below
("Aug/11/2009"). Exactly what input and formula is giving you a #Value! result?

Keep this information from the help file in mind:


Syntax

DATEVALUE(date_text)

Using the default date system in Excel for Windows, date_text must represent a
date from January 1, 1900, to December 31, 9999. DATEVALUE returns the #VALUE!
error value if date_text is out of this range.
 
Back
Top