Conditional Formatting based on dates.

[rusty21]

Hi All,

I have a spreadsheet to monitor when peoples legal documents are due for renewal. In cell D2-X2 I have the date that the current documentation expires. I want to format the cell so that if the current expiry date is past today it goes red, if todays date is within 2 months of the expiry date I want it to go Orange and anything outside of the 2 above I want green.

Any ideas please?

Regards
Rusty

 

[Joe4]

Multiple conditional formatting rules, using the formula options:

Formula for red condition:

=D2>TODAY()

Formula for orange condition:

=AND((TODAY()-D2)>0,(TODAY()-D2)<=60)

Formula for green condition:

=(TODAY()-D2)>60

 

[rusty21]

@Joe4,

Thankyou for the response. Unfortunately this doesn't work. No matter what the date is it is red permanently.

Regards
Rusty

 

[Sergius]

Hello! Will this suit you?

Book1.xlsm
	D	E	F
2	19.10.2024	10.09.2024	19.07.2024
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	Expression	=(TODAY()-F2)>60	text	NO
E2	Expression	=AND((TODAY()-E2)>0;(TODAY()-E2)<=60)	text	NO
D2	Expression	=D2=TODAY()	text	NO

 

[rusty21]

Hello! Will this suit you?

Book1.xlsm
	D	E	F
2	19.10.2024	10.09.2024	19.07.2024
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	Expression	=(TODAY()-F2)>60	text	NO
E2	Expression	=AND((TODAY()-E2)>0;(TODAY()-E2)<=60)	text	NO
D2	Expression	=D2=TODAY()	text	NO

Thank you for the reply, but that is the same as I was given in the previous response and just keeps it red.

Regards
Rusty

 

[Sergius]

It would be easier if you provided a piece of your data as is and as needed. I think in this case the help would be productive.

 

[MARK858]

It would also be helpful if you tested if your dates are real dates or are actually text.
Does the formula below return TRUE or FALSE.

=ISNUMBER(D2)

 

[rusty21]

It would also be helpful if you tested if your dates are real dates or are actually text.
Does the formula below return TRUE or FALSE.

=ISNUMBER(D2)

It returns TRUE.

 

[MARK858]

It returns TRUE.

With that result then we definitely need to see your actual data, preferably posted either using XL2BB or by uploading the actual file (data sanitized if needed) to a file hosting site like www.dropbox.com or www.box.com (remember to mark it for sharing and post the link it provides).

My take on it is to format manually all the cells as green (I see no need for a condition for this) then apply the rules below

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2	19/10/2024	10/09/2024	19/07/2024	02/09/2024	05/11/2024	04/10/2024	11/08/2024	23/07/2024	03/09/2024	25/08/2024	12/09/2024	15/08/2024	09/10/2024	05/11/2024	20/08/2024	02/10/2024	29/08/2024	13/08/2024	13/08/2024	14/08/2024	24/10/2024
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:X2	Expression	=AND((TODAY()-D2)>0,D2>=EDATE(TODAY(),-2))	text	NO
D2:X2	Expression	=D2>=TODAY()	text	NO