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")