Nested IF help

kal_el

New Member
Joined
Sep 12, 2005
Messages
8
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!
:)
 

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"))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top