DATEVALUE - Regional problem
 

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

DATEVALUE - Regional problem
 

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

HTH
--
AP

"EstherJ" a écrit dans le message de
news: ...
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")

DATEVALUE - Regional problem
 

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

"Ardus Petus" wrote:

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

HTH
--
AP

"EstherJ" a écrit dans le message de
news: ...
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")

DATEVALUE - Regional problem
 

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)

"EstherJ" wrote in message
...
This does not work for me with European settings - it does not like the
(2006,4,29) style

"Ardus Petus" wrote:

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

HTH
--
AP

"EstherJ" a écrit dans le message de
news: ...
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")