How to Use Conditional Formatting to Compare Dates in Excel

Get FREE Advanced Excel Exercises with Solutions!

Conditional formatting is a fundamental aspect of using Microsoft Excel. This enables us to locate certain values within a huge or random set of data. With conditional formatting, we can not only identify specific values but also values that are in or out of a range, string or numeric values, errors, etc. As the name implies, it involves formatting a cell in Excel according to whether or not a condition is true. You can use conditional formatting to compare dates also. It highlights specific dates based on the criteria. In this article, we are going to show you the way to use conditional formatting to compare dates in Excel. So, let’s get started.


How to Compare Dates with Conditional Formatting in Excel: 3 Simple Methods

We have demonstrated 3 easy and straightforward methods to compare dates with conditional formatting in Excel. We have taken a dataset of a List of Students with their Submission Dates of assignment.

Dataset

Not to mention, we have used the Microsoft Excel 365 version. You may use any other version at your convenience.


1. Comparing Dates Using Highlight Cells Rules

You can compare dates between two different dates. In our above dataset, we want to find the dates between 12/10/2022 and 12/31/2022. Follow the steps to do that.

📌 Steps:

  • Firstly, select the entire date column and navigate to the Home tab >> choose Conditional Formatting from the Styles group >> pick Highlight Cells Rules >> select the Between option.

Using Highlight Cells Rules to compare dates in excel conditional formatting

  • Eventually, the Between dialog box appears. Input the dates of 12/10/2022 and 12/31/2022 for comparison. Input Light Red Fill to fill the cells.
  • Lastly, hit OK.

Between window to compare dates in excel conditional formatting

Finally, you can see the dates between the two input dates are highlighted like the image below.


2. Applying New Rule to Compare Dates

We want to insert New Rule in Conditional Formatting to compare dates. For this, we have taken a Start Date and an End Date to see which dates are in between the two dates.

Follow the below steps to do that.

📌 Steps:

  • Initially, go to the Conditional Formatting of Styles group in the Home tab and choose New Rule.

Applying new rule to compare dates in excel conditional formatting

  • Consequently, the New Formatting Rule dialog wizard appears. Select Use a formula to determine within cells to format.
  • Moreover, write up the following formula in the Format values where this formula is true: box.
=AND($D5>=$C$18,$D5<=$C$19)

Here, $D5 represents the Submission Date column. Besides, $C$18, and $C$19 refer to “Start Date” and “End Date” respectively.

We checked whether the dates are in between the $C$18 and $C$19 cells.  The AND function combines the two arguments here.

  • Sequentially, click on Format.

New Formatting Rule to compare dates in excel conditional formatting

  • Subsequently, the Format Cells window pops out. Move to the Fill command. Choose a Color.
  • Lastly, press OK.

Format Cells to compare dates in excel conditional formatting

Finally, the dates are formatted with the color we chose between the two dates.


3. Utilizing Multiple Rules to Compare Dates

We can use multiple rules for 2 or more criteria on which the expected dates have lied. Suppose, you have a dataset where you inserted some dates with the present day with the TODAY function, some dates are below the dates, and others are ahead of the present day. Now, we will sort all the dates with different colors for comparison.

 📌 Steps:

  • Firstly, select the entire date range and then hover over to the Home tab >> pick Conditional Formatting >> click on New Rule.

utilizing Multiple Rules to compare dates in excel conditional formatting

  • Apparently, the New Formatting Rule window pops out. Choose to Use a formula to determine which cells to format.
  • Type the following formula in the Format values where this formula is true: box
=$D5=TODAY()

The D column is checked with the present date here.

  • Eventually, select Format.

  • Moreover, the Format Cells dialog box appears. Choose Fill and a color.
  • Lastly, hit OK.

Now, the present dates are formatted like the image below.

Additionally, go to the Conditional Formatting and select Manage Rules.

  • Subsequently, the Conditional Formatting Rules Manager window appears. Move to Edit Rule.
  • Similarly, write the rule for greater and smaller dates to the present date.

For dates greater than the present day:

$D5>TODAY()

For dates lesser than the present day:

$D5<TODAY()
  • Finally, hit OK.

Conditional Formatting Rules Manager

As you can see, your dates are formatted accordingly like the image below.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. These are some easy methods to compare dates with conditional formatting in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.


<< Go Back to Dates | Compare | 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.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo