[Solved!] Formulas to Compare Dates in Excel Not Working

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am going to discuss some possible cases when the formulas to compare dates are not working in Excel and devise some solutions to the problem. Some users may find comparing dates in Excel troublesome due to a lack of idea about how Excel stores dates. I will also cover this topic. After solving the problem, we will get the proper comparison result like this.

overview of compare dates not working


How Are Dates Stored in Excel?

Before troubleshooting the compare dates in Excel not working problem, we should have a solid idea about how Excel stores dates. You may be surprised but Excel stores date as numerical values (Integer)/ serial numbers. In Excel, the date starts from 1st January 1900. Hence, Excel assigns 1 to this date.

Stored Serial Number of 1 January 1900 in Excel

Similarly, Excel stores 2nd January 1900 as 2.

Stored Serial Number of 2 January 1900 in Excel

On the other hand, Excel stores the time as a decimal value. So, 1.50 is 1st January 1900, 12:00 PM.

Stored Serial Number of 1 January 1900, 12:00 PM in Excel

Therefore, when we compare 01/01/1900 with 02/01/1900, we are essentially comparing 1 with 2. For this storing system, we may see a date, formatted differently in different cells produce the same result though look completely different from each other. On the other hand, two dates may look the same but have different values and thus can produce unexpected results. This confusion is the primary source of inaccurate results while comparing dates in Excel.

Why Formulas to Compare Dates in Excel Are Not Working: 3 Possible Reasons and Solutions

In this section, I will explain the 3 most common reasons behind the problem when comparing dates in Excel does not work properly. I will also show how to solve those issues.  Let’s explore them one by one.

Reason 1: Date Inputs Are Considered Arithmetic Expressions in a Formula

Sometimes, Excel fails to recognize dates and takes them as Arithmetic Expressions. (see the Example below)

Date as Arithmetic Expression in Excel (compare dates in excel not working)

Here, we have compared 2nd February 2022 to 2nd February 2023 (02/02/2022 < 02/02/2023). Hence we expect Excel to give the result “TRUE”. But in the result, it gives “FALSE”. This is due to the fact that Excel considers the statement as an Arithmetic expression. Hence, we are essentially comparing 2÷2÷2022 to 2÷2÷2023.

Solution: Use the DATE Function to Directly Input Day, Month, or Year as Numbers

To solve the problem, we can use the DATE function. The DATE function allows us to directly input numbers as day, month, and year. In the formula bar, enter the dates in the following way.


Use of DATE function to compare dates in Excel

As a result, you will get an accurate answer.

Reason 2: Dates Are Formatted as Text

If the dates are formatted as text strings then the comparison might not work properly. For an example, see the screenshot below.

Dates Are Formatted as Text String

Here, we expect the result to be FALSE yet we got TRUE. If we investigate the formatting of the dates, we would see that both of the dates are in fact in Text format.

Dates Are Formatted as Text String

Solution: Change the Formatting to Date

To solve the issue, we need to change the Formatting of Dates to any suitable Date format. To do so, follow the steps below.

  • Firstly, select the two cells containing dates.
  • Secondly, click on the Formatting dropdown option.
  • Thirdly, choose the option Short Date.

Changing Format of Date from Text to Short Date

  • Now, re-enter the dates. As a result, you will have the desired result.

Comparison of Dates in Excel

Reason 3: There Are Hidden Time Values of Dates Which Are Not the Same

Sometimes, the time value of dates may not be visible but rather hidden and it can yield unexpected results. For example, see the screenshot below.

The Time Value of Dates Is Hidden

Here, we can see that even if the dates are exactly the same, we are getting FALSE as the result. If we investigate the actual value, we would notice that there are time values included with the Date value in cell B5.

The Time Value of Dates Is Hidden (compare dates in excel not working)

Solution: Change the Formatting of Cells to See Date and Time Together

Here, we need to change the formatting of the cells so that both cells are in the same format. To do that, follow the steps below.


  • Firstly, select the cells which contain dates.
  • Secondly, click on the Formatting dropdown option.
  • Thirdly, choose More Number Formats.

Chnaging Formatting of Cells (compare dates in excel not working)

  • As a result, a new dialog box named “Format Cells” will appear. From there, choose the format which consists of both date and time. Then finally, click

Changing Formatting of Dates in Excel

  • As a result, Excel will display the date and time as a whole and no confusion will occur.

Comparison of Dates in Excel

Things to Remember

  • As inappropriate formatting of dates is the main reason for unexpected comparison results of dates in Excel, always check the formatting of the cell containing dates.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


That is the end of this article regarding comparing dates in Excel not working. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< 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.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF