software

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 quot;out of reporting rangequot;.

=IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
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),quot;Out of reporting rangequot;)

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(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
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),quot;Out of reporting rangequot;)

=IF(AND(P2gt;=DATE(2006,4,29),P2lt;=DATE2009,8,28), ...

HTH
--
AP

quot;EstherJquot; gt; a écrit dans le message de
news: ...
gt; The following formula looks at the date in column P and brings back the
gt; corresponding date from a list in a sheet called DATE REF. If the date is
gt; outside of 29-APR-2006 and 28-Aug-2008 it returns quot;out of reporting
gt; rangequot;.
gt;
gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt;
gt; I had some help from the discussion group a while ago which lead me to use
gt; DATEVALUE as people in Asia and the USA were getting an error message.
gt; Now
gt; some Canadians have started to use my spreadsheet and they are getting the
gt; error message. How can I amend this to keep everyone happy? I am
gt; reluctant
gt; to ask them to amend their settings incase it causes them problem
gt; elsewhere.
gt;
gt; Any advice would be welocme.
gt;
gt; Thanks,
gt;
gt; Esther
gt;
gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt;
gt;
gt;
This does not work for me with European settings - it does not like the
(2006,4,29) style

quot;Ardus Petusquot; wrote:

gt; =IF(AND(P2gt;=DATE(2006,4,29),P2lt;=DATE2009,8,28), ...
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;EstherJquot; gt; a écrit dans le message de
gt; news: ...
gt; gt; The following formula looks at the date in column P and brings back the
gt; gt; corresponding date from a list in a sheet called DATE REF. If the date is
gt; gt; outside of 29-APR-2006 and 28-Aug-2008 it returns quot;out of reporting
gt; gt; rangequot;.
gt; gt;
gt; gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt; gt;
gt; gt; I had some help from the discussion group a while ago which lead me to use
gt; gt; DATEVALUE as people in Asia and the USA were getting an error message.
gt; gt; Now
gt; gt; some Canadians have started to use my spreadsheet and they are getting the
gt; gt; error message. How can I amend this to keep everyone happy? I am
gt; gt; reluctant
gt; gt; to ask them to amend their settings incase it causes them problem
gt; gt; elsewhere.
gt; gt;
gt; gt; Any advice would be welocme.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Esther
gt; gt;
gt; gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

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(P2gt;=--quot;2006-04-29quot;,P2lt;=--quot;2009-08-28quot;,
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),quot;Out of
reporting rangequot;)--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;EstherJquot; gt; wrote in message
news
gt; This does not work for me with European settings - it does not like the
gt; (2006,4,29) style
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; =IF(AND(P2gt;=DATE(2006,4,29),P2lt;=DATE2009,8,28), ...
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;EstherJquot; gt; a écrit dans le message de
gt; gt; news: ...
gt; gt; gt; The following formula looks at the date in column P and brings back
the
gt; gt; gt; corresponding date from a list in a sheet called DATE REF. If the
date is
gt; gt; gt; outside of 29-APR-2006 and 28-Aug-2008 it returns quot;out of reporting
gt; gt; gt; rangequot;.
gt; gt; gt;
gt; gt; gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; gt; gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt; gt; gt;
gt; gt; gt; I had some help from the discussion group a while ago which lead me to
use
gt; gt; gt; DATEVALUE as people in Asia and the USA were getting an error message.
gt; gt; gt; Now
gt; gt; gt; some Canadians have started to use my spreadsheet and they are getting
the
gt; gt; gt; error message. How can I amend this to keep everyone happy? I am
gt; gt; gt; reluctant
gt; gt; gt; to ask them to amend their settings incase it causes them problem
gt; gt; gt; elsewhere.
gt; gt; gt;
gt; gt; gt; Any advice would be welocme.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; Esther
gt; gt; gt;
gt; gt; gt; =IF(AND(P2gt;=DATEVALUE(quot;29-APR-2006quot;),P2lt;=DATEVALUE(quot;28-AUG-2009quot;),
gt; gt; gt; ISNUMBER(INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; gt; REF'!$A$1:$A$1820,0),3))),INDEX('DATE REF'!$A$1:$H$1820,MATCH(P2,'DATE
gt; gt; gt; REF'!$A$1:$A$1820,0),3),quot;Out of reporting rangequot;)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;