excel - Conditional Formatting Rows Based on Date

Question

I have a table with a round 500 rows of data. I want to setup conditional formatting to highlight rows based upon how old a row is (based upon the date cell in that row). The idea is that as an entry get's older, it is entered with a white background, but as it ages, it turns a darker shade of red. We have data taken from 5 separate weeks and plan to add more. Is this POSSIBLE in Excel, or will I need to highlight each week manually?

Answer 1

If you are willing to have one column be highlighted, as opposed to the entire row, this example should help.

Steps to reproduce:

• Highlight the column with the dates
• In the Conditional Formatting menu (Under the Home Tab on the Ribbon - Office 2013)
• New Rule
• Format All Cells based on their values.
• Choose the darkest color for the Lowest Value
• Choose the lightest color for the Highest Value
• OK

Note in the example below how the scale of the colors changes based on the range. When the lowest date is 2001, that receives the darkest red, but when a date from 1980 is entered, the dates from 2001 and up are considered to be in the lighter third.

The formatting applies to the entire column, so you can add as many dates as you need.

Here are some very useful tips demonstrating a number of uses for conditional formatting:

• Excel formulas for conditional formatting based on another cell value

• How to conditionally format dates and time in Excel - built-in rules and formulas