While working with Dates in Excel, sometimes we have to differentiate some data based on their dates. It is a good practice to change the cell Color of the cells based on their dates maintaining some specific criteria. Here in this article, I will be showing some ways to do this comfortably.

Download Practice Workbook

How to Change Cell Color Based on Date in Excel

Let us have a look at this datasheet. We have the Delivery Schedule of a Company named APEX Group.

Changing the Cell Color of Only the Cells with the Dates

1. Changing the Cell Color of the Dates Less than a Particular Date.

We want to change the Cell Color of the Delivery Dates, which are less than a given date, to Light Red. Think for a moment this particular date is June 10. How do we do that?

First of all, select all the Dates. Here I am selecting all the Dates between cells B4 to B12.

Then in the Excel Toolbar, go to Home>Conditional Formatting option under Styles Section.

Then click the Drop Down menu with it. You will get these options.

Click on the first option, Highlight Cells Rules. You will get more options like this.

Click on Less Than. You will get a small box like this.

In the Format Cells that are LESS THAN option, write down the particular date less than which you want to highlight. Here I have chosen it to be 10-Jun-21. Then click the Drop Down menu of the ‘with’ box. You will get these options.

These are the Formats you can use to change the specific cells. If you want more, you can go to the last option, Custom Format and make a Format of your wish. Here I am selecting Light Red Fill.

Then click OK.

You see, all the cell colors containing Dates less than June 10 have been changed to Light Red.

Note: If you want to change the color of Dates greater than a particular date, choose Greater Than option from Highlight Cells Rules and do the same.  You can also find out the Dates Equal to a certain date too. Just choose the right option from the Highlight Cells Rules option.

2. Changing the Cell Color of the Dates in between Two Particular Dates.

Now we want to change the cell color of the Dates from June 05 to June 15 as Light Red.

How can we do that? Here I am showing.

Select the Dates (Cell B4 to B12) again, go to Home>Conditional Formatting option under Styles Section. Click the Drop Down menu. Click on Highlight Cells Rules. You will get the same options as earlier. This time click on the option Between.

You will get a small box like this.

In the Format cells that are BETWEEN option, write down the Dates. I am writing 05-Jun-21 and 15-June-21. 

Then click the dropdown menu with “with”.

Again select the Format you like. I am choosing Light Red Fill. Then click OK.

You see, all the cell colors containing Dates between June 05 and June 15 have been changed to Light Red.

3. Changing the Cell Color of the Dates with a Particular Day (Sunday)

Now we want to change the Cell Colors which contain Dates with a Particular Day (Sunday). We will again change the color to Light Red.

Here we will use Excel’s WEEKDAY function. WEEKDAY is a very simple function. It takes an argument as a Date and gives a number between 1 to 7 as output. Each number indicates a specific day. Number 1 indicates Sunday, Number 2 indicates Monday and so on. Number 7 indicates Saturday.

Select all the Dates (B4:B12).

Go to Home>Conditional Formatting option under Styles Section. Click the Drop Down menu. Click on New Rule.

You will get a box like this.

Under Select a Rule Type menu, click on the last option. Use a formula to determine which cells to format. You will get more options like this.

In the Format values where this formula is true option, insert this formula. =WEEKDAY(B4:B12)=1.

If you do not know about the WEEKDAY function, go to section 3. There I have discussed it in details.

Now click on the Format option. You will get the Format Dialogue Box like this.

Choose the Color you want. I am choosing Red. Now click OK. You see, cell B4, B8 and B12 have been changed to Red. Because They are all on Sunday.

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.

4. Changing the Cell Color of Dates Within Some Specific Days

We can also change the color of all the Dates within some specific days, like last 7 days, last 1 month etc. To do that, Select the Dates (Cell B4 to B12) again, go to Home>Conditional Formatting option under Styles Section. Click the Drop Down menu. Click on Highlight Cell Rules. Then select A Date Occurring option.

Click it. You will get a box like this.

Click on the left drop down menu. You will get options like this.

Now select the one as you wish. I want all the days within the last 7 days, so I choose the In the last 7 days option.

And from the right drop down menu, select the format you want. Here I want Light Red color.

Then click OK. 

You will find all the Dates within the last 7 days will be colored Light Red.

Conclusion

Using these methods, you can change the color of cells based on the dates. This is really very useful in our daily work with Excel. If you know any other method, let us know in the comment section.

The post Excel Formula to Change Cell Color Based on Date appeared first on ExcelDemy.