Why is DATEVALUE assuming that dates are in "dd/mm/yyyy" format?

G

Guest

In Excel 2003 SP2, in cells using "March 14, 2001" date format with "Locale
(location)" set to "English (United States)":

=DATEVALUE("22-AUG-2008") displays as "August 22, 2008"
=DATEVALUE("2008/08/22") displays as "August 22, 2008"
=DATEVALUE("22/08/2008") displays as "August 22, 2008"
=DATEVALUE("22/08/08") displays as "August 22, 2008" and warns about the
two-digit year
=DATEVALUE("8/2/2008") displays as "February 8, 2008"
=DATEVALUE("08/02/2008") displays as "February 8, 2008"
=DATEVALUE("08/22/08") gives a #VALUE! error
=DATEVALUE("8/22/2008") gives a #VALUE! error
=DATEVALUE("08/22/2008") gives a #VALUE! error

I'm assuming this is controlled by a configuration setting of some kind, but
which one? The "Short Date" format in the Regional and Language Options
control panel is set to "MM/dd/yyyy" format, and as noted the cells
themselves are displaying with the U.S. locale. The "8/22/2008" example is
even used on the DATEFORMAT documentation page...
 

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

Similar Threads

How do I change dd/mm/yy to dd-month-yy? 6
Dates from Text 4
Problem with DATEVALUE 16
DATEVALUE, etc. and Australian date format 1
Date field not stay in same format 1
Format Date "dd/mm/yyyy" 1
SAVEDATE Format dd/mm/yyyy hh:mm:ss 4
Format duration in mm/dd hh:mm 3

Top