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

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.

Similarly, Excel stores 2nd January 1900 as 2.

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

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)

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.

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.

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.

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.

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

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.

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.

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.

• 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

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

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.

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.

<< Go Back to Dates | Compare | Learn Excel