Conditional Formatting on Tables

[richtks]

This has worked for me in the past, but I can't for the life of me, get this to work. is it because I'm using tables... ugh...

Delemma...

I want to format ROW-T based on ROW-T's date and ROW-U's text.

Basically - =T2<today(),U2="p", and I want the format to result in "08/02/2024"

I've tried =and($T$2:$T$158<today(),$U$2:$U$158="P") - as well as =and($T2:$T158<today(),$U2:$U158="P")

And eventually, I want to "White out" dates in ROW-T if an "N" or an "A" is in ROW-U

 

[etaf]

then select the range
T2:U100
and use
=AND($T2<today(),$P2="P")
format with strikethrough

XL2BB not showing the strickthrough

and for white out
use
=OR($U2="A",$U2="N")

Book9
	T	U
1	Column1	Column2
2	1/1/24
3	1/2/24
4	1/3/24
5	1/4/24	p
6	1/5/24	p
7	1/6/24
8	1/7/24
9	1/8/24
10	1/9/24
11	1/10/24	p
12	1/11/24
13	1/12/24
14	1/13/24
15	1/14/24	p
16	1/15/24
17	1/16/24
18	1/17/24
19	1/18/24
20	1/19/24
21	1/20/24	p
22	1/21/24
23	1/22/24
24	1/23/24
25	1/24/24
26	1/25/24
27	1/26/24
28	1/27/24
29	1/28/24
30	1/29/24
31	1/30/24
32	1/31/24
33	2/1/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T1:U33	Expression	=AND($U1="P",$T1<TODAY())	text	NO

Book9
	T	U
1	Column1	Column2
2	1/1/24
3	1/2/24
4	1/3/24
5	1/4/24	p
6	1/5/24	p
7	1/6/24
8	1/7/24
9	1/8/24	a
10	1/9/24	a
11	1/10/24	p
12	1/11/24
13	1/12/24
14	1/13/24
15	1/14/24	p
16	1/15/24
17	1/16/24
18	1/17/24	n
19	1/18/24	n
20	1/19/24
21	1/20/24	p
22	1/21/24
23	1/22/24
24	1/23/24
25	1/24/24
26	1/25/24
27	1/26/24
28	1/27/24
29	1/28/24
30	1/29/24
31	1/30/24
32	1/31/24
33	2/1/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T1:U33	Expression	=OR($U1="N",$U1="A")	text	NO
T1:U33	Expression	=AND($U1="P",$T1<TODAY())	text	NO

 

[richtks]

Awesome! Thanks!!!

 

[etaf]

you are welcome