Conditional formatting

Kabook4511

New Member
If need to condition my spreadsheet to highlight cells green if date in cell is less than 8 months from today's date, highlight yellow if date in cell is between 8 and 11 months old and red if date in cell is 12 months or older.

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Eric W

MrExcel MVP
Welcome to the forum!

Try:

Where the individual formulas are:

Book1
F
1
2
3
4
51/1/2020
64/1/2020
710/1/2020
82/10/2021
91/1/2022
10
11
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:FExpression=(F1<>"")*(F1<EDATE(TODAY(),-12))textNO
F:FExpression=(F1<>"")*(F1>EDATE(TODAY(),-8))textNO
F:FExpression=(F1<>"")*(F1>EDATE(TODAY(),-12))textNO

To enter the formulas, select the column, click Conditional Formatting > New Rule > Use a Formula, and enter the formula and select the formatting. Make sure to change the column in the formula to match your selected column. You also need to make sure that the Green formula precedes the Yellow formula.

Active Member
Test.xlsm
E
522/06/2020
622/05/2020
722/02/2020
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E5:E7Cell Value<TODAY()-330textNO
E5:E7Cell Valuebetween TODAY()-241 and TODAY()-330textNO
E5:E7Cell Valuebetween TODAY()-1 and TODAY()-240textNO

Kabook4511

New Member
Thank you all! I ended up figuring it out and used following formulas:
Red
=DATEDIF(C4,TODAY(),"y")>=1

Yellow
=DATEDIF(C4,TODAY(),"m")>=9

Green
Cell does not contain blanks

White
If cell is blank

Replies
8
Views
319
Replies
19
Views
97
Replies
5
Views
324
Replies
3
Views
42
Replies
1
Views
66

1,127,827
Messages
5,627,124
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back