How to Change Cell Color Based on Date Using Excel Formula

While working with dates in Microsoft Excel, sometimes users have to differentiate some data based on their dates. It is good practice to change the cell color of the cells based on their dates, maintaining some specific criteria. In this article, I will show you how to change cell color based on date using Excel formula.

Download Practice Workbook

You can practice on your own by downloading the free Excel workbook here.

4 Easy Ways to Change Cell Color Based on Date Using Excel Formula

This article will show you four different ways to use an Excel formula to change the color of a cell based on the date. Here, I will use the Conditional Formatting feature of Excel to apply these methods. By going through different conditions of this feature, I will change the cell color based on the date.

To demonstrate my article further, I will use the following sample data set.

1. Change Cell Color of Dates Based on Particular Value

In the first method, you will see the cell colors being changed based on a specific value. You can use the value to indicate dates that are greater than or less than it.

1.1 Greater Than Particular Date

Suppose you want to change the cell color of the existing dates that are greater than a particular date. To highlight or change cell color based on these criteria, you have to follow some specific steps. The steps to complete this procedure are as follows.

Steps:

• First of all, select the cell range C5:C13 and go to the Home tab of the ribbon.
• Then, under the Styles group, select Conditional Formatting.

• Secondly, from the dropdown select Highlight Cells Rules, and under this criterion, choose Greater Than.

• Thirdly, enter the specific date with that you want to compare and choose the highlighting style.
• After confirming the above two conditions, press OK.

• Finally, the cells containing dates greater than 11 September 2022 will change their colors after the previous step.

1.2 Less Than Particular Date

What will be the procedure to change the cell colors that are less than a particular date? If you want to find out, then go through the following steps.

Steps:

• Firstly, again go to the Conditional Formatting command just like the previous method after choosing the cell range for highlighting.
• Then, from the Highlight Cells Rules dropdown, select Less Than.

• Secondly, insert the date in the Less Than dialog box for comparison and specify the cell style after highlighting.
• Lastly, press OK.

• Finally, you will find the cells with dates highlighted that are less than 12 September 2022.

Read More: How to Make Negative Numbers Red in Excel (3 Ways)

2. Change Cell Color of Dates Between Two Particular Dates

Secondly, I want to change the cell color of the dates from 10 September to 25 September to yellow. How can I do that? You will find the answer in the following steps.

Steps:

• In the beginning, select the cell range C5:C17.

• Secondly, select Conditional Formatting from the Home tab of the ribbon.
• Then, from the Highlight Cells Rules dropdown select Between.

• Thirdly, in the Format cells that are BETWEEN option, write down the above two dates.
• Then click the dropdown menu with and select the format you like.
• Lastly, click OK.

• Finally, after applying the above conditions the cells’ color will look like the following image.

Read More: Excel Conditional Formatting Dates

Similar Readings

• Find and Replace Text Color in Excel (3 Quick Ways)
• How to Apply Different Types of Conditional Formatting in Excel
• If Cell Color Is Red Then Execute Different Functions in Excel
• Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)
• How to Do Conditional Formatting Highlight Row Based On Date

3. Change Cell Color of Dates with Particular Day

As for the third method, I want to change the cell colors that contain dates to a particular day, i.e., Sunday. Here, I will use the WEEKDAY function. It takes an argument as a date and gives a number between 1 and 7 as an output. Each number indicates a specific day. Number 1 indicates Sunday, number 2 indicates Monday, and so on. See the following steps for a better understanding.

Steps:

• First of all, after selecting the required cell range, go to the Home tab of the ribbon.
• Then, from the dropdown after clicking Conditional Formatting, select New Rule.

• Secondly, under the Select a Rule Type menu, click on the last option which is Use a formula to determine which cells to format.
• Then, in the Format values where this formula is true option, insert the following formula.

• After that, press Format to select the cell color and font style.

• Thirdly, after setting all the criteria, press OK in the dialog box.

• Finally, after finishing the previous step, the date that represents Sunday will be highlighted.

Read More: Excel Conditional Formatting Based on Date Range

4. Change Cell Color of Dates Within Some Specific Days

You can also change the color of all the dates within some specific days, like the last 7 days, the last 1 month, etc. To change the cell colors of dates for a particular period, see the below-given steps.

Steps:

• Firstly, select the dates from cell range C5:C17 and go to the  Home tab of the ribbon.
• Then, select the Conditional Formatting option under the Styles section.
• After that, click the dropdown menu and select Highlight Cell Rules.
• Afterward, choose the A Date Occurring option.

• Secondly, you will see the A Date Occurring dialog box.
• Here, click on the left drop-down menu and you will get options like this.

• Thirdly, select the one you wish.
• Consequently, I want all the days within the last month to be highlighted, so I choose the Last month option.
• Then specify the style criteria and press OK.

• Finally, you will see all the days from the previous month that is September will get highlighted after the previous steps.

Read More: How to Use Conditional Formatting in Excel Based on Dates

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to change cell color based on date using Excel formula. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

Related Articles

• Excel Conditional Formatting Based On Another Cell Date (4 Ways)
• Formula to Change Text Color Based on Value
• Excel Conditional Formatting for Dates within 30 Days (3 Examples)
• How to Sum in Excel If the Cell Color Is Red (4 Easy Methods)
• Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
• How to Make Yes Green and No Red in Excel (8 Examples)
• Excel If Cell Color Is Green Then Show or Customize Outputs