"Is Between" Function Solution --- Many Thanks

[Cindi]

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

Thanks Again for everyone's help!

Cindi

 

[ivano]

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

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

Thanks Again for everyone's help!

Cindi

ciao Cindi,
why not:
=VLOOKUP(TODAY(),{38275,1;38285,2;38293,3;38299,4;38306,5;38313,6;38320,7},2
)

where 38275 is serial number of 2004-10-15
38285 is serial number of 2004-25-10
and so on...

ivano

 

[Harlan Grove]

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

(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.