Text to Date

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

 

[Luke M]

Change to appropriate cell reference:
=DATEVALUE(SUBSTITUTE(A1,"/",", "))

 

[PAL]

Shouldn't this work also....

=IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))

 

[Glenn]

Yes, it should. Are you saying it isn't?

Shouldn't this work also....

=IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))

 

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

 

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

 

[PAL]

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

 

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

 

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