Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

 


Download Practice Workbook

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


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


3 Possible Reasons Why Formulas to Compare Dates in Excel Are Not Working and Their 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 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.

=DATE(2022,2,2)<DATE(2023,2,2)

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.

Steps:

  • 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 name “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, now  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.

Conclusion

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. Finally, please visit ExcelDemy for more exciting articles on Excel.

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo