Conditional Formatting Formula Help

[Arts]

Hi all

I am using conditional formatting so that if the date in a cell is "less than" todays date the cell turns red which works fine (rule being used screen shot below)

The issue I am having is that if the cell is left blank it remains red. Is there anyway I can add a rule with the date rule that if the cell is blank it remains blank (no colour) but if a date entered less than today then turn red.

In short if cell is blank = "No fill".
If cell has date < today turn red.
If cell has date > today remain white.

Many thanks
Arts

 

[etaf]

i would setup a formula
AND( cell < today() , cell <> "" )

Book2
	A
2	4/1/22
3	4/2/22
4	4/3/22
5	4/4/22
6	4/5/22
7	4/6/22
8	4/7/22
9	4/8/22
10	4/9/22
11	4/10/22
12	4/11/22
13	4/12/22
14	4/13/22
15	4/14/22
16	4/15/22
17	4/16/22
18	4/17/22
19	4/18/22
20	4/19/22
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=AND(A2<TODAY(), A2<>"")	text	NO

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:A100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(A2 <> "" , A2 < today() )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[Fluff]

In conditional formatting, New Rule, Use a formula & use

=AND(B1<>"",B1<TODAY())

Change the B1 to the 1st cell in the applies to range

 

[Arts]

Thank you both for your responses, works exactly as I had hoped!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[etaf]

you are welcome