Excel Formula to Change Cell Color Based on Date

Dates less than a particular date are selected in Excel.

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 Excel Formulas 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.

A data set in Excel.

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.

A column of dates is selected in Excel.

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

Conditional Formatting option in Excel.

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

Conditional Formatting option clicked in Excel

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

Highlight Cells Rules option is clicked in Excel

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

Less Than Dialogue box in Excel

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.

Less Than Dialogue box in Excel

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.

Dates less than a particular date are selected in Excel

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.

Between option in Excel

You will get a small box like this.

Between box in Excel

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

Between box in Excel

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.

Dates between two particular dates are selected in Excel

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.

New Rule option in Excel

You will get a box like this.

New Formatting Rule box in Excel

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.

New Formatting Rule box in Excel

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.

Inserting Formula into New Formatting Rule box in Excel

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

Format Dialogue Box in Excel

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.

All Cells with Sunday are changed to red in Excel

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.

A Date Occurring option in Excel

Click it. You will get a box like this.

A Date Occurring box in Excel

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

A Date Occurring box in Excel

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.

A Date Occurring box in Excel

Then click OK. 

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

Dates within last 7 days are selected in Excel

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo