# Nested IF help

#### kal_el

##### New Member
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?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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"))

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.

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
ABCD
1x
27/29/20068/13/20068/31/2006
3OVERDUECLOSEDOPEN
Sheet1

Replies
7
Views
297
Replies
6
Views
128
Replies
7
Views
483
Replies
14
Views
502
Replies
3
Views
255

1,217,449
Messages
6,136,696
Members
450,025
Latest member
Beginner52

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back