EXCEL 2007 DATEVALE FUNCTION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Back
Top