Need help with IF(AND) function

[wpavone]

I'm working on a project scheduling tool. L7 contains a start date and M7 contains a finish date. K1 contains the current day's date. Progress towards completion is measured as: Green if L7 is greater than 14 days prior to K1; Yellow if L7 is between 14 days greater and today's date; Red if L7 is past today's date; Blue once M7 is entered. No value is returned if L7 is blank. So far I have the following nested IF statement: =IF(AND(M7="",L7<$K$1),"R",IF(L7="","",IF(M7<=$K$1,"G",IF(M7>0,"B",IF(SUM(M7-L7<14),"Y",""))))). As written, this is returning a value of Red when L7 is blank. I've re-written this multiple different ways but I just can't figure out how to write it so the values return correctly. Help! Any suggestions will be appreciated.

 

[Mike Blackman]

Hi and welcome to the board,

Does something like this work?

=IF(M7<>"","Blue",LOOKUP(L7-K1,{-10000,"Green";-14,"Yellow";1,"Red"}))

 

[wpavone]

Changed the LOOKUP value to K1-L7 instead of L7-K1 and it works like a charm. Thanks!!!