IF Argument with nested AND conditions via conditional format

BlackRazor

New Member
OK,

Hi everyone. First post, long time site fan. The community is awesome and great to see it growing.

Now heres my formula problem.

I have a spreadsheet with a frozen split for column headers. It has two contiguous columns which contain dates. I have a date format applied to the cells. The actual columns are J and K. Column J contains the date I sent a request to a client. Column K contains the date I received the requested documents.

Here is my algebraic logic sequence, but I'm not sure how to translate into a formula,er, exactly. I have a good idea of what operants and querants to use, so please feel free to correct my formulaic logic if something is wrong.

I would like to use a nested IF argument to apply a conditional format to cells in Column K (Cell X) if Cell X IS BLANK, AND the value of cell immediately to its left (Cell Y) is NOT=>TODAY()-30.

In lamens terms to help conceptualize the formula, it needs to look at the date I sent out the request for documents to a client and then look at the cell to its right, which is the date I received the documents back. If the cell adjacent to the right of the request date is blank AND if that request was within the past 30 days, then it should apply a conditional format. If the request date is not within the past 30 days, then there should be no conditional formatting. Also, if the cell in Column K is not blank and actually has a date, then there should be no conditional formatting.

I know some of the information above is extraneous, but it helps to conceptualize the spreadsheet. I thank you all in advance, and can't wait to get my formula.

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
CF>Formula IS
Code:
``=((\$K2)*(\$J2<=TODAY()-30))``
Copy the format down as needed
lenze

Even though I figured out the logic sequence, I can't get the formula figured out. Could someone give the correct entire formula, please. Also some instructions would be nice. Thank you. ..and thanks for the help Lenze.

Maybe I misunderstood your OP. I guess you want
Code:
``=(ISBLANK(\$K2))*(\$J2<=TODAY()-30)``
Select K2 and choose Format>Conditional Formatting. Select the Formula Is option. Enter the above. Note, that CF is already an IF statment, so you don't need it in your formula

My gut feeling, however, is you might want
Code:
``=(ISBLANK(\$K2))*(\$J2>=TODAY()-30)``

lenze

Replies
2
Views
815
Replies
0
Views
523
Replies
7
Views
2K
Replies
9
Views
288
Replies
3
Views
287

1,196,115
Messages
6,013,558
Members
441,771
Latest member
clamnets

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.

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