IF Statements

[angusn]

I'm missing something but can't see for the lack of coffee.

B6 - 21-Feb-18 (Due Date)
L1 - =today()
E9 - 20-Jan-18 (Start Date)
F9 - Currently Empty (Completed Date)
G9 - =IF(A9="","",IF(F9="",$B$6-$L$1,"Done")) (Days remaining till past due/days past due)
H9 - =IF(E9="","",IF(ISBLANK(F9),"In Progress",IF(G9<=0,"Past Due","Complete"))) (Problem Child)

I'm trying to get H9 to show "Past Due".

When I have a date in E9 and F9 is blank and the task is not past due (G9 is a positive number), Cell H9 shows In Progress. Great.

When I have a date in E9 and a date in F9, Cell H9 shows Complete. Great.

When I have a date in E9 and F9 is blank and the task is past due (G9 is a negative number, Cell H9 Does Not show Past Due. This is what I can't get to work.

Thanks in advance.

 

[taccoo73]

Hi angusn;

Your formula looks fine, I believe the issue is the way you've built it. G9 populates a number if F9 is empty and your formula in H9 populates "In Progress" as far as F9 is empty.

Try =IF(E9="","",IF(G9<0,"Past Due",IF(F9<>"","Done","In Progress")))

 

[jtakw]

Hi,

What's A9???

 

[angusn]

jtakw - A9 is for the name of the task needed to be done. Just hard typed as needed.


taccoo73 - That's working. So I needed to get the Past Due in before adding the empty F9 cell trigger into the mix. I get that. Thanks.