Text to 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.
 
T

T. Valko

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.
 
R

Rick Rothstein

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.
 
P

PAL

Correct. It is not. I get the pesky, #Value!. Perhaps ISERROR would work,
but it isn't either.
 
R

Roger Govier

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.
 
G

Glenn

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top