I have a column in excel of multiple dates and some blanks, I need to highlight the cells different colors based on their date range calculating from Today. Example, cells with no date a certain color, cells older than 1 day but less than 6 months a certain color, cells older than 6 months but less than 11 months a certain color, cells older than 11 months a certain color.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Conditional formatting to highlight multiple date ranges in column calculating from today
- Thread starter JacqueM
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 73,795
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
Highlight your whole column that you want to apply this to, then add the following Conditional Formatting Rules, as they pertain to the first cell in your highlighted range (Excel will automatically adjust for all the other rows). So if the first cell in your range is A1, here is what those formulas will look like:
A few notes:
- Don't forget to choose the appropriate color formatting option for each rule.
- When you enter the Conditional Formatting Rules in, the newest rule added gets put at the top. So you either want to enter these rules in reverse order, or re-order them after you have entered them all (you can do that from the "Manage Rules" section).
- Note that you may need to "edit" the rules slightly, as you did not mention what should happen if it EXACTLY 6 months or 11 months. You will probably just need to change the appropriate > to >=, etc.
Highlight your whole column that you want to apply this to, then add the following Conditional Formatting Rules, as they pertain to the first cell in your highlighted range (Excel will automatically adjust for all the other rows). So if the first cell in your range is A1, here is what those formulas will look like:
Code:
Rule 1: =$A1=""
Rule 2: =AND($A1 < TODAY()-1,$A1 > EDATE(TODAY(),-6))
Rule 3: =AND($A1 < EDATE(TODAY(),-6),$A1 > EDATE(TODAY(),-11))
Rule 4: =$A1 < EDATE(TODAY(),-11)A few notes:
- Don't forget to choose the appropriate color formatting option for each rule.
- When you enter the Conditional Formatting Rules in, the newest rule added gets put at the top. So you either want to enter these rules in reverse order, or re-order them after you have entered them all (you can do that from the "Manage Rules" section).
- Note that you may need to "edit" the rules slightly, as you did not mention what should happen if it EXACTLY 6 months or 11 months. You will probably just need to change the appropriate > to >=, etc.
Upvote
0
Similar threads
- Question
