Datevalue gives problems when the dates are text and not formatted in the
users regional date format. Example if you have a text date 4/6/2009 if the
users regional date format is m/d/y then it converts it to apr 6 2009 but if
the users regional date format is d/m/y then it converts it to 4 Jun 2009.
Using the Text to columns you are telling it what format the text dates are
in and therefore it converts them accordingly.

--
Regards,

OssieMac


"Fred Smith" wrote:

If Datevalue works, it means your fields are Text, not dates.

You can use DataText to Columns to convert them, if you'd like.
You can also use Datevalue, as you did -- now just format the result as a
date.

If you're getting a #Value error, it may look identical, but isn't. You
likely have trailing blanks or unprintable characters in the cell. Something
as simple as:
=Datevalue(Trim(a1))
might work for you.

Regards,
Fred.

"MaryJC" wrote in message
...
I received an Excel file from a customer that has a date field where the
dates look like 7/13/2005 or 10/14/2005 - they all look the same and I've
tried applying the same format to all. However, if I try to use the
DATEVALUE function, it works on one portion of them and returns numbers
like
38546 and 38639 which is what I want. Another whole portion that looks
identical returns the error #VALUE! - I cannot seem to figure out how or
why
they are being handled differently. Thanks for any help!

.