Nested IF help

[kal_el]

Hello all,

I am having a really difficult time trying to figure this out and was hoping someone here could help.

I have an action item sheet with the following:

- A “check mark” (A1) cell that the user enters an “x” if the action item is closed.

- And a DUE DATE cell (A2).

- A STATUS (A3) cell that reads: CLOSED, OPEN, or OVERDUE.

If A1 is open (no x) AND A2 is past 30 days from TODAY() then I want A3 to read “OVERDUE”. Otherwise it should read OPEN. Unless (and this is the part I can’t figure out) A1 has an X, then A3 should read CLOSED.

I am so close (I think I am anyway), but just can’t figure it out.

=IF(A1="x","CLOSED",IF(A1="","OPEN",IF((TODAY()-A2)>30, "OVERDUE")))

Can anyone point me in the right direction?

Thanks for any help you can provide!

 

[Oaktree]

Assuming A1 will either have an x or be blank, your IF(A1="" test will always be TRUE, so you'd always get "OPEN" if A1 is blank or "CLOSED" if A1 = x (from the first test).

Also note that your formula calculates older than 60 days as overdue but your note says 30 days. Adjust the 60 to 30 if necessary.

Try:

=IF(A1="x","CLOSED",IF((TODAY()-A2)>60, "OVERDUE","OPEN"))

 

[kal_el]

Assuming A1 will either have an x or be blank, your IF(A1="" test will always be TRUE, so you'd always get "OPEN" if A1 is blank or "CLOSED" if A1 = x (from the first test).

Also note that your formula calculates older than 60 days as overdue but your note says 30 days. Adjust the 60 to 30 if necessary.

Try:

=IF(A1="x","CLOSED",IF((TODAY()-A2)>60, "OVERDUE","OPEN"))

Wow! You were too fast for me. I said 30, but meant 60 and went back to edit the post.

I tried this and it didn't work. The status cell won't change back.

 

[Oaktree]

Make sure calculation is set to automatic and that A2 stores an Excel date.

A3 =IF(A1="x","CLOSED",IF((TODAY()-A2)>30, "OVERDUE","OPEN")), copied right...

Book2
	A	B	C	D
1		x
2	7/29/2006	8/13/2006	8/31/2006
3	OVERDUE	CLOSED	OPEN
Sheet1