EXCEL 2007 DATEVALE FUNCTION

[John]

I have recently installed Office 2007. I have noticed an incompatibility with
a number of Excel spreadsheets that I have created in Excel 2000, 2003. The
issue is with the DATEVALUE() function. It appears that in earlier versions
Excel accepted text in the following format "MM/DD/YYYY"

It now appears that in Excel 2007 that the format is "DD/MM/YYYY". The
interesting thing is that Excel 2007 help still refers to the original
"MM/DD/YYYY" format.

For example in Excel 2000/2002/2003: DATEVALUE("12/31/2006") results
in 39082

In Excel 2007: DATEVALUE("12/31/2006") results in #VALUE!

I was wondering if anyone else has encountered this, has a workaround or if
there is a setting to change this compatibility. Otherwise I am terribly
disappointed in Microsoft.

 

[David Biddulph]

Have a look at your regional settings in Windows/ Control Panel.

 

[Teethless mama]

It works fine on my XL2007

 

[Roger Govier]

Hi John

It is exactly as David has said, down to Regional setting.
Here in the UK, I get #VALUE with
DATEVALUE("12/31/2006")
but I get 39082 with
DATEVALUE("31/12/2006")

If I change my Regional settings to English US, then the situation is
reversed from that shown above.