How to Change Cell Color Based on Date Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Changing Cell Color Based on Date


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

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.

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


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.

Change Cell Color of Dates Greater Than Particular Date as An Easy Way to Change Cell Color Based on Date Using Excel Formula

  • 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.

Change Cell Color of Dates Less Than Particular Date as An Easy Way to Change Cell Color Based on Date Using Excel Formula

  • 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: Highlighting Row with Conditional Formatting Based on Date in Excel


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.

Change Cell Color of Dates Between Two Particular Dates as An Easy Way to Change Cell Color Based on Date Using Excel Formula

  • 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 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.
 =WEEKDAY(B4:B12)=1
  • After that, press Format to select the cell color and font style.

Change Cell Color of Dates with Particular Day as An Easy Way to Change Cell Color Based on Date Using Excel Formula

  • 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.

Note:

  • If you want to identify any other day than Sunday, write the equivalent number of that day in the Formula Bar of New Formatting Rule Box.

Read More: Conditional Formatting Based on Date in Another Cell in Excel


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.

Change Cell Color of Dates Within Some Specific Days as An Easy Way to Change Cell Color Based on Date Using Excel Formula

  • 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: Apply Conditional Formatting for Dates Older Than Today in Excel


Download Practice Workbook

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


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.


Related Articles

<< Go Back to Conditional Formatting Based on Date | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. Hi,
    I found this really useful but when I try to change a cell according to the DAY of the week the weekday function is returning more than one day. Eg, 1 is returning changes for Thu and Sun. Do you know why this is?
    Thanks for your help!!

    • Hi Jennifer,
      I think you have issues with your dates. Make sure your dates are accurate and in proper date format. Confirming your dates, you can apply the WEEKDAY function again. Still, if you suffer from this problem, it’s better to check the format that you’ve applied. To highlight Sunday you will apply the following formula: =WEEKDAY(B4:B12)=1. Make sure that the range inside the WEEKDAY function is legit. If everything goes just fine, this formula will highlight all the Sundays throughout your dates.
      If nothing works for you, I would suggest you send your Excel file to my mail address: [email protected]. I will see what’s wrong with your data.
      Thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo