Conditional Formatting

[NZAS]

Hi
I have a column of Dates that I would like to add conditional formatting so the cell will hilight when the date is equal to today minus 30 days. And if the date is between 30 and 60 and if date is between 60 to 90 using different colours for each

12/12/2021​ 15/04/2022​ 7/02/2022​ 29/10/2022​ 10/10/2022​ 25/03/2022​ 13/02/2022​ 20/02/2022​ 24/02/2022​ 6/02/2022​

​

​

​

​

​

​

​

​

​

​

​

​

 

[etaf]

do you mean exactly -30 days
and which colour for the 60 days - its in both is it 30-60 or in 60-90
what if older than 90


setup 3 conditional formatting rules

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:
=A2=today()-30

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

then 2 more rules using
=AND( A2 >= today()-60 , A2 < today()-30 )
=AND( A2 >= today()-90 , A2 < today()-60 )

just change the >= or <= to include the = depending on what group you want

Book4
	A	B
1	1/11/22	-30
2	1/6/22	-35
3	12/17/21	-55
4	12/7/21	-65
5	11/17/21	-85
6	11/2/21	-100
7	1/26/22	-15
8	1/10/22	-31
9	12/7/21	-65
10	2/9/22	-1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()-30
A2	A2	=TODAY()-35
A3	A3	=TODAY()-55
A4,A9	A4	=TODAY()-65
A5	A5	=TODAY()-85
A6	A6	=TODAY()-100
A7	A7	=TODAY()-15
A8	A8	=TODAY()-31
A10	A10	=TODAY()-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=AND(A1>=TODAY()-90,A1<TODAY()-60)	text	NO
A1:A10	Expression	=AND(A1>=TODAY()-60,A1<TODAY()-30)	text	NO
A1:A10	Expression	=A1=TODAY()-30	text	NO