Conditional formatting

Kabook4511

New Member
Joined
Feb 10, 2021
Messages
2
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Welcome to the forum!

Try:

1612980199140.png


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.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Kabook4511

New Member
Joined
Feb 10, 2021
Messages
2
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,343
Messages
5,624,118
Members
416,012
Latest member
rockermom59

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top