How to Change Cell Color Based on Date in Excel


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.

Repeat these steps to apply the other two rules.

In the Conditional Formatting Rules Manager panel, we’ll be able to see all three rules:

Excel change color based on date

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Apply Conditional Formatting Based on Multiple Text Values
Excel: Apply Conditional Formatting Based on Multiple Conditions
Excel: Apply Conditional Formatting if Cell is Between Two Dates

Leave a Reply

Your email address will not be published. Required fields are marked *