Help! PRobelms with TODAY()?!

[Guest]

I've got a formula that works great for me:
=IF(A2="","",IF(BH2="","",IF(AE2="Closed","",IF(BH2<=TODAY()-120,"Over
120",IF(BH2<=TODAY()-90,"90-119",IF(BH2<=TODAY()-60,"60-89",IF(BH2<=TODAY()-30,"30-59","0-29")))))))

My last "IF" statement, however, is not quite right. I need to include to
change the last "if false" statement to add IF(BH2<TODAY(),"1-20","0") so
that if the date is BH2 has not occured yet (IE, not yet overdue), the I'll
just have a zero in the cell.

When I add this, it looks like:

=IF(A2="","",IF(BH2="","",IF(AE2="Closed","",IF(BH2<=TODAY()-120,"Over
120",IF(BH2<=TODAY()-90,"90-119",IF(BH2<=TODAY()-60,"60-89",IF(BH2<=TODAY()-30,"30-59",IF(BH2<TODAY(),"1-29","0"))))))))

But I get an error with the formula. When I analyze the function, I get the
error stating my last TODAY() statement is "VOLATILE". I can't find what
that means, or how to fix?! Any ideas?

KSL.

 

[Guest]

I think the problem you've run into is that you've exceeded Excel's limit of
7 levels of nested functions in a formula. You could actually combine your
first three IFs into one though, since they all result in "" if TRUE. Try
this:

=IF(OR(A2="",BH2="",AE2="Closed"),"",IF(BH2<=TODAY()-120,"Over
120",IF(BH2<=TODAY()-90,"90-119",IF(BH2<=TODAY()-60,"60-89",IF(BH2<=TODAY()-30,"30-59",IF(BH2<TODAY(),"1-29",0))))))

HTH,
Elkar

 

[T. Valko]

Try this:

=IF(OR(A2="",BH2="",AE2="Closed"),"",LOOKUP(TODAY()-BH2,{-10000000,1,30,60,90,120},{0,"1-29","30-59","60-89","90-119","Over
120"}))