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

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

HTH
--
AP

"EstherJ" <EstherJ@discussions.microsoft.com> a écrit dans le message de
news: 238AEB25-A33D-4ED4-9231-219865F9D912@microsoft.com...
> 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")
>
>
>

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" <EstherJ@discussions.microsoft.com> a écrit dans le message de
> news: 238AEB25-A33D-4ED4-9231-219865F9D912@microsoft.com...
> > 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")
> >
> >
> >
>
>
>

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" <EstherJ@discussions.microsoft.com> wrote in message
news:D0106802-18E1-489A-86EF-EA295DCACE46@microsoft.com...
> 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" <EstherJ@discussions.microsoft.com> a écrit dans le message de
> > news: 238AEB25-A33D-4ED4-9231-219865F9D912@microsoft.com...
> > > 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")
> > >
> > >
> > >
> >
> >
> >