Excel 2010 using IF AND formula

Question

I am trying to create a formula that says iF A1 is completed Show 0. (ignoring others cell) If A1 it is not completed and is not past the due date(Cell B2) , Show 1,

if it is not completed and past the due date show 3.

Based on 1,2 or 3 I can then display what I want (in this case Icons for Red, Yellow or Green)

This is the closet formula I can come up with, but I am not there...

=IF(AND(D2="Yes",B2>TODAY(),),1,0)

Answer 1

You can do:

=IF(A1="Yes",0,IF(B1>$F$1,1,3))

Where F1 = Today()

Example:

No  11/11/2012          1   10/24/2012
Yes 10/12/2012          0   
Yes 11/13/2012          0   
No  9/14/2012                    3  
No  11/15/2012          1   
Yes 11/16/2012          0   
Yes 11/17/2012          0   
No  10/18/2012          3