conditional formatting if NA

[rjmdc]

hi this is my conditional formatting formula
=AND(L1<=TODAY()-365)
how do i exclude if column A isNOT blank then do the formatting unless leave blank

 

[jtakw]

Hi,

I recommend using EDATE (accounts for leap years) rather than -365 in your formula (assuming you're trying to see if it's a year or more ago):

Book3.xlsx
	A											L	M	N
4												12/28/2019	FALSE	FALSE
5	a											12/28/2019	TRUE	TRUE
6												3/15/2021	FALSE	FALSE
7	b											3/15/2021	FALSE	FALSE
Sheet747

Cell Formulas
Range	Formula
M4:M7	M4	=AND(A4<>"",L4<=TODAY()-365)
N4:N7	N4	=AND(A4<>"",L4<=EDATE(TODAY(),-12))

 

[rjmdc]

neither work it hilites the wrong dates when i correct the formulas

 

[jtakw]

I think I had it backwards...

exclude if column A isNOT blank then do the formatting

Try these:

Book3.xlsx
	A											L	M	N
4												12/28/2019	TRUE	TRUE
5	a											12/28/2019	FALSE	FALSE
6												3/15/2021	FALSE	FALSE
7	b											3/15/2021	FALSE	FALSE
Sheet747

Cell Formulas
Range	Formula
M4:M7	M4	=AND(A4="",L4<=TODAY()-365)
N4:N7	N4	=AND(A4="",L4<=EDATE(TODAY(),-12))

 

[rjmdc]

hi
i had to use this one
=AND(A1<>"",L1<=TODAY()-365)
then it worked
thanks

 

[jtakw]

You're welcome.

That's what I posted in post #2, you just had to change the cell reference(s) according to your data.
I still recommend using EDATE over -365 if you're testing for a year or older...