Multiple criteria

[Sherri Martin]

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

 

[strooman]

Is this your lay-out?

Row\Col	C​	D​	E​	F​	G​	H​	I​
1​	Start Date	Due Date	Last Name	First Name	Class	Section	Date of Course
2​	2/16/2018	8/16/2018	XXXX	XXX	Active Shooter Action Plan
3​	2/16/2018	5/16/2018	XXXX	XXX	Annual Protecting Electronic)		2/22/2018
4​			XXXX	XXX	Ethics in State Government

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

 

[Sherri Martin]

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]

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]

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