Sherri Martin

New Member
Joined
Jan 17, 2006
Messages
6
I am using conditional formatting. I have two columns that have dates. Column D2 "Due Date" and Column I2 the date the course was take.

I want to highlight the cell in red if the date is past due. I don't want it to do. So if D2 is blank and I2 is blank, do nothing, but if I2 is blank and D2 is past todays date, highlight in RED. It is highlight the blank cells in D column and the ones in I2 that have a date.

I am very frustrated!!


This is my formula
=IF($D2="",$I2="",(D7<TODAY()))

Start Date Due Date Last Name First Name Class Section Date of Course
2/16/2018 8/16/2018 XXXX XXX ​ Active Shooter Action Plan
2/16/2018 5/16/2018 XXXX XXX Annual Protecting Electronic) 2/22/2018
XXXX XXX Ethics in State Government
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
Is this your lay-out?

Row\Col
C​
D​
E​
F​
G​
H​
I​
1​
Start DateDue DateLast NameFirst NameClassSectionDate of Course
2​
2/16/20188/16/2018XXXXXXXActive Shooter Action Plan
3​
2/16/20185/16/2018XXXXXXXAnnual Protecting Electronic)2/22/2018
4​
XXXXXXXEthics in State Government

If yes, what should be highlighted? Please post the desired result.
 

Sherri Martin

New Member
Joined
Jan 17, 2006
Messages
6
Yes, it look great!

Highlight in Blue if due date ($D2) is within 30 days and it has not been Completed in (H2).
Highlight in Peach if due date ($D2) is within 60 days and it has not been Completed in (H2).
Highlight in Green if due date ($D2) is within 90 days and it has not been Completed in (H2).
Highlight in Red if due date is past ($D2) and it has not been Completed in Cell (H2).

If (D2)is blank and and there is a Date in (I2) do nothing

There are times that there is not due date needed. The due dates are only for new staff.

I hope this helps. Thank you for responding. I am have been working on this for week now and very frustrated. I am new at all this.
 

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
Still a few questions. Try to clarify. You write:
"Highlight in Blue if due date ($D2) is within 30 days and it has not been Completed in (H2)"

Within 30 (60, 90) days of what date? The date in column I?
H2 is empty. How can one see it's completed?
I understand you want to highlight the dates in column D. But we need information against what dates we should compare. Please provide the desired results in a screenshot. You can replace privacy sensible information with dummy data.
 

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
why not just insert another column and highlight based on that?
say col J = if(C3="Y",J3="Y",[Due Date] - [Start Date])
=$J1="Y" Green A:J
=$J1<30 Red Columns A:J
=$J1<60 Blue Columns A:J
=$J1<90 Yellow Columns A:J
 

Watch MrExcel Video

Forum statistics

Threads
1,114,367
Messages
5,547,508
Members
410,797
Latest member
mlfuson
Top