"Cindi" 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,i f(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.