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
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!
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!
Related:
- Conditionally Formatting if another column contains a specific date
- How to find specific words on a page - Guide
- How do i find a specific video on youtube - Guide
- How to change date format in excel - Guide
- How to delete a column in word - Guide
- How to clear formatting in excel - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Updated on May 27, 2021 at 12:10 PM
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
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
