Highlight cell if another cell contains ANY date value whatsoever

[RRico]

Hi guys! Seeking to use conditional formatting to Highlight a cell if another/different cell contains ANY date value whatsoever. I'm specifically trying to make it so that Column "I" (eye phonetically) will format to standard black font (vs. the current rule I have to make it red when the date is past today's date). I'd like to make it go back to black if/when a date value is inserted into Column "L".

I'm able to do this with non date values... but for a date value, it's not accepting any of my usual formulas

Any help please?

 

[etaf]

a date is just a number - so what else is in column L

in column I formatting you already have add a condition for L

=AND(I2<TODAY(),NOT(ISNUMBER(L2)))

Book4
	I	J	K	L	M
1
2	6/1/23			12/1/23	TRUE
3	6/2/23
4	6/3/23			12/1/23
5	6/4/23
6	6/5/23
7	6/6/23
8	6/7/23
9	6/8/23
10	6/9/23
11	6/10/23
12	6/11/23
13	6/12/23
14	6/13/23
15	6/14/23
16	6/15/23
17	6/16/23
Sheet1

Cell Formulas
Range	Formula
M2	M2	=ISNUMBER(L2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I17	Expression	=AND(I2<TODAY(),NOT(ISNUMBER(L2)))	text	NO

 

[RRico]

Interestingly... your formula is working.... (sort-of), but it's working in the opposite manner. Currently all dates are red in Column I due to today being greater than (older than) the date value sitting in column I. When I use your formula, it's taking the red off the font in Column I (bad result), and when I enter a date value in Column L, then it's making it go back to red for some reason?

 

[etaf]

Currently all dates are red in Column I due to today being greater than (older than) the date value sitting in column I.

I have used I2<TODAY() so if I2 is less than - which means the date is older than today ...

today being greater than - so 1 aug 24 is greater than today - BUT then you say OLDER than
not sure i follow

BUT change the formula to

=AND(I2>TODAY(),NOT(ISNUMBER(L2)))

Book4
	I	J	K	L
1
2	6/1/23			12/1/23
3	6/2/23
4	6/3/23			12/1/23
5	6/4/23
6	6/5/23
7	6/6/23
8	6/7/23
9	6/8/23			2/1/23
10	6/9/23
11	6/10/23
12	6/11/23
13	6/12/23
14	6/13/23			2/1/23
15	6/14/23
16	6/15/23
17	6/16/23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I17	Expression	=AND(I2>TODAY(),NOT(ISNUMBER(L2)))	text	YES

 

[RRico]

Ah, OK... it looks like all I had to do was delete the "red" rule that I had in there prior to consulting with you I think we're OK Now. I will do some more testing, but if/when I find this to have worked, I'll Mark as solution" button - right next to this post.

 

[etaf]

you are welcome

 

[RRico]

This all worked GREAT! Thank you so much for your help on this!!!

 

[etaf]

the range applied to does not start i2 , it starts at I25 , so will be 23 rows out

also are you deleting or inserting rows ?

applies to I2:I38 in both caes and make sure the formula also starts at row 2