DATEVALUE - Regional problem

[Guest]

The following formula looks at the date in column P and brings back the
corresponding date from a list in a sheet called DATE REF. If the date is
outside of 29-APR-2006 and 28-Aug-2008 it returns "out of reporting range".

=IF(AND(P2>=DATEVALUE("29-APR-2006"),P2<=DATEVALUE("28-AUG-2009"),
ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
REF'!$A$1:$A$1820,0),3),"Out of reporting range")

I had some help from the discussion group a while ago which lead me to use
DATEVALUE as people in Asia and the USA were getting an error message. Now
some Canadians have started to use my spreadsheet and they are getting the
error message. How can I amend this to keep everyone happy? I am reluctant
to ask them to amend their settings incase it causes them problem elsewhere.

Any advice would be welocme.

Thanks,

Esther

=IF(AND(P2>=DATEVALUE("29-APR-2006"),P2<=DATEVALUE("28-AUG-2009"),
ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
REF'!$A$1:$A$1820,0),3),"Out of reporting range")

 

[Ardus Petus]

=IF(AND(P2>=DATE(2006,4,29),P2<=DATE2009,8,28), ...

HTH

 

[Guest]

This does not work for me with European settings - it does not like the
(2006,4,29) style

 

[Bob Phillips]

Esther,

Ardus uses European settinmgs too, as I do, and we have no problem with it.
It is format independent, specifying year, month, day.

You could also try

=IF(AND(P2>=--"2006-04-29",P2<=--"2009-08-28",
ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
REF'!$A$1:$A$1820,0),3))),
INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE REF'!$A$1:$A$1820,0),3),"Out of
reporting range")


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)