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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
 

Forum statistics

Threads
1,136,514
Messages
5,676,293
Members
419,619
Latest member
jalme

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
Top