Conditional Formatting Formula for Dates

[cTatch]

Hey Y'all,

I'm trying to figure out a conditional formatting formula for cells O17-O30 that highlights date cells that are either in the past, today, or up to 3 months in the future, all relative to today's date.

Does anyone know a good formula for that?

Thank you!
Chelsea

 

[Saurabhj]

Hi,

Use below:

=O17<=EDATE(TODAY(),3)

 

[cTatch]

Hi,

Use below:

=O17<=EDATE(TODAY(),3)

Hi, not sure why, but I could get that to work - when I used that formula it didn't highlight the correct cells. Any ideas? I've posted the date cells I'm using here. Thank you.

1/31/2021​

1/31/2021​

4/30/2019​

9/30/2021​

9/30/2021​

8/31/2021​

12/31/2020​

 

[Fluff]

How about

+Fluff v2.xlsm
	O
17	31/01/2021
18
19	31/01/2021
20
21	30/04/2019
22
23	30/09/2021
24
25	30/09/2021
26
27	31/08/2021
28
29	31/12/2020
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O17:O29	Expression	=AND(O17<>"", O17<=EDATE(TODAY(),3))	text	NO

 

[cTatch]

How about

+Fluff v2.xlsm
	O
17	31/01/2021
18
19	31/01/2021
20
21	30/04/2019
22
23	30/09/2021
24
25	30/09/2021
26
27	31/08/2021
28
29	31/12/2020
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O17:O29	Expression	=AND(O17<>"", O17<=EDATE(TODAY(),3))	text	NO

That worked perfectly! Thanks so much, Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.