Forum Discussion
Conditional formatting if date is 7 days past AND there is no date in another column
I am trying to get cells in Date Sent for Review (column O) to highlight if date in column is 7 days ago AND there is no date in column Date Approval Received (column P).
Date Sent for Review Date Approval Received
| 1/12/2024 | 1/12/2024 |
| 1/12/2024 | 1/12/2024 |
| 1/12/2024 | |
| 1/22/2024 | 1/22/2024 |
| 1/23/2024 | |
| 1/29/2024 | 2/5/2024 |
| 1/29/2024 | 2/5/2024 |
| 1/29/2024 | 1/31/2024 |
| 1/29/2024 | 1/31/2024 |
| 1/31/2024 | 2/1/2024 |
Select O2:O100 or as far down as you want.
O2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND($O2<>"", $O2<=TODAY()-7, $P2="")
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.- THANK YOU! I spent hours googling and trying different formulas and could not get it to work right. This worked perfectly!
