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.

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

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

It works fine on my XL2007


"John" wrote:

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.

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.
--
Regards

Roger Govier


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