How to Do Conditional Formatting Based on Date in Excel

This guide will discuss how to do conditional formatting based on date in Excel.

Excel is a powerful tool to use for different purposes and situations. Since it contains several built-in functions and tools, we can efficiently perform complex tasks such as long calculations, data analysis, and creating graphs.

Another thing we can easily and quickly do in Excel is format the cells in our data set depending on certain conditions or criteria. We can utilize conditional formatting in Excel to auto-change the format of specific cells based on their contents.

Moreover, conditional formatting is a powerful feature in Excel that is used to automatically apply highlights or format to cells. More importantly, we can utilize this feature to ensure specific dates in our data set stand out.

Furthermore, there are several methods we can use to apply conditional formatting on cells based on specific dates. This guide will explore the different methods to do conditional formatting based on dates.

So we can simply apply conditional formatting based on dates using an already available rule. However, we can also create a new rule and apply built-in functions to cater to specific situations. 

Let’s take a sample scenario wherein we need to do conditional formatting based on date in Excel.

Suppose you have created a data set containing the dates of when products have been ordered. And you want to highlight cells containing last week’s dates to keep the data set updated weekly. 

To do this, you decided to apply conditional formatting in your data set. Specifically, you used the highlight cells rules and chose a date occurring.

Before we move on to a real example of doing conditional formatting based on date in Excel, let’s first learn some functions we can use.

 

 

The Anatomy of the TODAY Function

The syntax or the way we write the TODAY function is as follows:

=TODAY()

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • TODAY() is our TODAY function. And this function is used to return the current date formatted as a date. Moreover, it is continuously updated whenever we open or change the worksheet. 

 

The Anatomy of the NOW Function

The syntax or the way we write the NOW function is as follows:

=NOW()

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • NOW() refers to our NOW function. And this function is used to return the current date and time formatted as a date and time. Similarly, this will also automatically update to the current date as time goes on.

Great! Now we can dive into a real example of doing conditional formatting based on date in Excel.

 

A Real Example of Doing Conditional Formatting Based on Date in Excel

Let’s say we have a data set containing the project timeline. So we have a column showing the tasks needed to be done and the dates for each task. So our initial data set would look like this:

Initial dataset

 

Moreover, we want to highlight the cells containing dates that have passed based on the current date. For example, we want to highlight a cell in the red since the date for the task has already passed. 

Luckily, there are two ways we can perform this. Firstly, we can simply use a built-in conditional formatting rule that Excel already has for dates. Otherwise, we can also create a new rule using functions.

Since conditional formatting is a powerful and flexible tool, we can adjust our rules on what cells to highlight depending on the data set. In this case, we only want to highlight cells containing dates that have passed.

Firstly, we can easily do this by using the built-in conditional formatting rule of a date occurring. Then, we can simply choose the condition yesterday. So all cells that have yesterday’s date will be highlighted. 

Secondly, we can create a new rule using the TODAY function or the NOW function. In this example, we can detect past and future dates based on the current date. So we will utilize the TODAY or NOW functions to identify the current date.

Then, we can highlight all cells that are before the current date. And we can now easily identify tasks that should have been completed by the current date. So our final data set would look like this:

final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and discuss the steps of how to do conditional formatting based on date in Excel.

 

How to Do Conditional Formatting Based on Date in Excel

In this section, we will discuss the step-by-step process of how to do conditional formatting based on date in Excel. Furthermore, each step has pictures and detailed instructions to guide you along the way.

1. Firstly, we will select the cells we want to highlight. Then, we will go to the Home tab and click Conditional Formatting. In the dropdown menu, we will select Highlight Cells Rules and choose A Date Occurring.

Conditional Formatting Based on Date in Excel

 

2. Secondly, we will select Yesterday in the dropdown menu. Then, we can also choose the specific color format. Lastly, we will click Done to apply the changes.

Conditional Formatting Based on Date in Excel

 

3. And tada! We successfully did conditional formatting based on date in Excel using the built-in rule.

Final dataset

 

4. Now let’s try creating a new rule using the TODAY or NOW functions. To do this, we will select the cells we want to apply the conditional formatting on. Then, we will go to the Home tab and select Conditional Formatting. In the dropdown menu, we will choose New Rule.

Conditional Formatting Based on Date in Excel

 

5. In the New Formatting Rule window, we will select Use a formula to determine which cells to format. Next, we will input the formula “=NOW()”. Otherwise, we can also use the formula “=TODAY()”. 

Then, we can choose the format we want. Lastly, we will click Done to apply the changes.

Conditional Formatting Based on Date in Excel

 

6. And tada! We have successfully highlighted cells containing past dates.

Final output

 

And that’s pretty much it! We have successfully discussed how to do conditional formatting based on date in Excel. Now you can choose any of the two methods and apply them to your work whenever necessary.

Are you interested in learning more about what Excel can do? You can now use the TODAY function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

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

You May Also Like