Conditional Formatting from multiple columns

[rasinc]

I am trying to do some conditional formatting on data in a table. I would like the dates in columns I and J to be filled red when the date in both columns is less than Jan 1/20, green when the date in each column is greater or equal to Jan 1/23 and yellow if between those two dates but all scenarios only if column E is a quantity greater than zero.

I currently have the red setup for dates as

=AND(I2<DATE(2020,1,1),J2<DATE(2020,1,1))

I'd like to say it is working but it is colouring some cells with dates in 2023 and 2024 in each column and others that have both dates in 2016 are not coloured at all, and I haven't included the quantity yet.

I have a mental block on these conditional formulas for some reason. I tried setting up each column separately but had the same results.

Any help is greatly appreciated.

 

[etaf]

try a $
=AND($I2<DATE(2020,1,1),$J2<DATE(2020,1,1),$E2>0)

Green
=AND($I2>=DATE(2023,1,1),$J2>=DATE(2023,1,1),$E2>0)

Yellow - is that both are between the dates

less than 1/1/2020 and greater than 1/1/2023. so not possible

OR do you mean between

1/1/2020 and 1/1/2023

And E > 0


=AND($I2>=DATE(2020,1,1) , $I2<=DATE(2023,1,1) ,$J2>=DATE(2020,1,1), $J2<=DATE(2023,1,1) , $E2>0)

 

[rasinc]

Thank you for your reply. I was not able to get it to work with one formula so I set the data up in a table, filtered for Qty <>0 and set up three cells with the criteria dates. Then I set up the rules referencing the cells. This does work and gets me what I need. Thank you again.

 

[etaf]

I was not able to get it to work with one formula

no, as i mentined you would need 3 - one for each colour
OR 2 if you setup one of the fill colours a a default

 

[rasinc]

try a $
=AND($I2<DATE(2020,1,1),$J2<DATE(2020,1,1),$E2>0)

This formula that checks dates in two columns and qty is what I was referring to. I had to filter the table to the quantity then use the conditional formula on the dates. It's working well now.

 

[MARK858]

I had to filter the table to the quantity then use the conditional formula on the dates

Are you sure that your numbers in column E are real numbers and not text? E5 in the XL2BB below is text.

Book2
	E	F	G	H	I	J
2	2	TRUE			15/12/2009	17/12/2009
3	2	TRUE			15/12/2009	01/02/2020
4	0	TRUE			15/12/2009	17/12/2009
5	0	FALSE			15/12/2009	17/12/2009
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=ISNUMBER(E2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J5	Expression	=AND($I2<DATE(2020,1,1),$J2<DATE(2020,1,1),$E2>0)	text	NO