Text to Date

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.

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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.

Shouldn't this work also....

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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.


.

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

PAL wrote:
Shouldn't this work also....

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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 DataText 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.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

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.

--
Rick (MVP - Excel)



"PAL" wrote in message
...
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.

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

"Glenn" wrote:

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

PAL wrote:
Shouldn't this work also....

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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.

.

.

Hi

Did you not try Biff's suggestion of
Datatext to ColumnsNextNextDateM/D/Y

That works perfectly and is the easiest way to go IMO.
--
Regards
Roger Govier

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

"Glenn" wrote:

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

PAL wrote:
Shouldn't this work also....

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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.
.

.

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.


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

"Glenn" wrote:

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

PAL wrote:
Shouldn't this work also....

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
"PAL" wrote in message
...
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.
.

.