Help! PRobelms with TODAY()?!

L

Leonhardtk

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

Elkar

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

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"}))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"LOOKUP" within a condition? 2
More Conditional Formatting 1
if statement - help needed 7
Syntax help with date calculation 1
SUMIF with date ranges 2
Aging Report 7
IF statement help 1
Timebase help!!! 1

Top