Previous Topic Next Topic

Conditionally Formatting if another column contains a specific date

Solved/Closed
cjf15 - May 26, 2021 at 03:45 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 27, 2021 at 12:07 PM
Hello,

I have a spreadsheet where I am tracking requests. I want Excel to highlight the row if the item due date (column B) is NEXT month or has PASSED (for when we receive late requests), but I only want it to highlight if the 'Date Completed' (column I) is not filled in. Once it is filled in, I want the highlight to go away.

I have tried multiple variations using both the AND, IF & ISBLANK function and cannot figure it out. Please help!

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on May 27, 2021 at 12:10 PM
Hi cjf15,

The due date being next month or being passed will fit in the same condition:
MONTH($B2)<=MONTH(TODAY())+1

To NOT highlight the row when the corresponding cell in column B is empty (so you can apply the CF to rows which don't contain a due date yet):
$B2<>""

To ONLY highlight the row when the corresponding cell in column I is empty:
$I2=""

We then combine the above in an AND formula:
=AND(MONTH($B2)<=MONTH(TODAY())+1,$B2<>"",$I2="")

Best regards,
Trowa

0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Highlight cell if another cell contains any text
LvH -
LvH -

4 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Conditional format based on another cell containing text
stakik -
Corrina66 -

5 responses