Table of Contents
In Excel, you can use the Conditional Formatting feature to change the background color of a cell based on its content. You can set a rule that will compare the value in the cell to a specific date, and if the value is equal to or after that date, the background color of the cell will change. You can also set rules to change the color of a cell based on the content of other cells or based on the value of a formula.
To change cell color based on date in Excel, you can use the Manage Rules option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: How to Change Cell Color Based on Date in Excel
Suppose we have the following column of dates in Excel:
This article is being written on 11/20/2023.
Suppose we would like to change the color of the cells in column A based on the following rules:
- If the date is within 5 days or less, make the color red.
- Else, if the date is within 30 days or less, make the color yellow.
- Else, if the date is greater than 30 days away, make the color green.
To apply these rules, highlight the range A2:A11, then click the Conditional Formatting dropdown menu on the Home tab, then click Manage Rules:
In the new window that appears, click the New Rule button:
Then click Use a formula to determine which cells to format, then type =A2<=TODAY()+5 in the box, then click the Format button and choose a red fill color.
Lastly, click OK.
In the Conditional Formatting Rules Manager panel, we’ll be able to see all three rules:
Then click OK.
The cells will have now have a specific color based on the date:
Note: In this example we used three rules but you can use the same process to apply as many rules as you’d like.