Re: "Is Between" Function Solution --- Many Thanks

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Harlan Grove (hrlngrv_at_aol.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 14:04:10 -0800

"Cindi" <anonymous@discussions.microsoft.com> wrote...
>Thanks to all that helped me figure this out. I finally
>did get it to work, after trying each suggestion. The
>suggestion that I found to work was as follows:
>
>=if(and(today()>=date(2004,10,15),today()<=date
>(2004,10,24)),1,if(and(today()>=date(2004,10,25),today()
><=date(2004,11,01)),2,if(and(today()>=date
>(2004,11,02),today()<=date(2004,11,07)),3,if(and(today()
>>=date(2004,11,08),today()<=date(2004,11,14)),4,if(and
>(today()>=date(2004,11,15),today()<=date(2004,11,21)),5,if
>(and(today()>=date(2004,11,22),today()<=date
>(2004,11,28)),6,if(and(today()>=date(2004,11,29),today()
><=date(2004,12,06)),7,0)))))))))

Maybe this works in OpenOffice Calc, but it's guaranteed not to work in
Excel because it uses more than 7 levels of nested function calls.
Specifically, Excel should choke on the expressions

today()>=date(2004,11,29)

and

today()<=date(2004,12,06)

So what's your real formula?

Dominic's MATCH solution is much, much better than this. The formula above
is redundant. If you insist on using a convoluted nested IF approach, at
least eliminate the unnecessary comparisons. Clearly, if the date falls
outside 10/15/04 and 12/06/04, you want 0, so

=IF(OR(TODAY()<--"10/15/04",TODAY()>--"12/06/04"),0,
IF(TODAY()>--"11/28/04",7,IF(TODAY()>--"11/21/04",6,
IF(TODAY()>--"11/14/04",5,IF(TODAY()>--"11/07/04",4,
IF(TODAY()>--"11/01/04",3,IF(TODAY()>--"10/24/04",2,1)))))))

This could work in Excel.

Quantcast