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!

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!

Hi Mary,

Ensure that you have a backup of your workbook before doing any of the
following because I cannot be certain that my assumption of text is accurate.

I suspect that the dates in your worksheet are text and if my assumption is
correct then you should be able to use Text to Columns to fix the problem.

Select the column of dates.

Select Menu item Data - Text to columns (or in xl2007 Ribbon Data then Text
to columns)

Select Fixed width then Click Next (Data preview is displayed.)

Click Next again.

Another data preview should be displayed with black background (selected)

Under Column data format section click date button.

Click the drop down against date and select MDY (That is the date format of
your existing data)

Click Finish.

Your column should now be valid dates but in the wrong format. (MDY)

Select Number format and format the dates in your required format. (DMY)

--
Regards,

OssieMac

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!

.