Cell to change colour when a date is about to expire

[Sharr76]

Hi all,

I have tried my best and I just cant get this to work:

How would I get a white cell with a date in it to change colour, e.g. to orange when the date is about to expire, so I would like cells to change to orange when a date is due to expire in 12 weeks time, 84 days.

 

[etaf]

conditional formatting

As today is 19/may/21 - then due to expire in 84 days time = 11th Aug 21
so any date earlier than 11th Aug 21 to the past will highlight

=today() + 84
that adds 84 days to todays date

then if cell < = today() + 84
is a formula that will give use dates from the past upto 84 days in future

not sure what range the dates are in
assume B2 to z200

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

Highlight applicable range >>

B2 to Z200


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( B2<>"", B2 < = today() + 84)


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

The AND is to stop Blank cells highlighting

chemistry_v1.xlsx
	B	C	D	E	F	G	H
2
3		8/11/21
4				8/13/21
5						8/15/21
6		5/19/21
7
8				5/31/21
9
10
11
12
Sheet9

Cell Formulas
Range	Formula
C3	C3	=TODAY()+84
E4	E4	=TODAY()+86
G5	G5	=TODAY()+88
C6	C6	=TODAY()
E8	E8	=TODAY()+12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:M12	Expression	=AND(B2<>"",B2<=TODAY()+84)	text	NO

 

[Sharr76]

Hi, what am I doing wrong? I still cant get this to work?

 

[etaf]

its put inverted commas around the formula and on the ""

="AND( B2<>"""", B2 < = today() + 84)" - those extra "" needs removing, edit the formula ad remove the RED "

to become

=AND( B2<>"", B2 < = today() + 84)