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

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.

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.

Read More: How to Compare If Date Is Before Another Date 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)

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.

As a result, you will get an accurate answer.

Read More: Excel Conditional Formatting for Date Less Than 6 Months from Today

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.

Read More: Excel Formula If One Date is Greater Than Another Date

Similar Readings

• Excel Formula If Date Is Less Than Today (4 Examples)
• How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
• Excel Formula If Date Is Greater Than 2 Years (3 Examples)
• Conditional Formatting Based on Date Older Than 1 Year in Excel
• Excel Conditional Formatting for Date Within 3 Months (3 Methods)

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 name “Format Cells” will appear. From there, choose the format which consists of both date and time. Then finally, click

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

Read More: If Cell Contains Date Then Return Value in Excel (5 Examples)

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.

Related Articles

• How to Use Conditional Formatting to Compare Dates in Excel
• Excel Conditional Formatting Based on Past or Due Date
• Check If Date Is Within 7 Days of Another Date in Excel (7 Ways)
• How to Use COUNTIF for Date Greater Than 30 Days in Excel
• Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
• How to Compare Dates to Today with Excel VBA (3 Easy Ways)
• Conditional Formatting for Dates Older Than Certain Date in Excel