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

Here’s an overview of formulas not working when comparing dates. We’ll show the most common reasons behind these issues and how to fix them.

How Are Dates Stored in Excel?

In Excel, the date starts from 1st January 1900. Excel assigns 1 to this date.

Then, Excel stores 2nd January 1900 as 2.

Excel stores the time as a decimal value with the same starting point. This means 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. Due to the implicit storing in an integer format, formulas that assume that something is a date but is formatted differently might not work as intended.

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

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

Sometimes, Excel fails to recognize dates and takes them as arithmetic expressions.

Here, we have compared 2nd February 2022 to 2nd February 2023 (02/02/2022 < 02/02/2023). 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, comparing 2÷2÷2022 to 2÷2÷2023.

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

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)

Reason 2 – Dates Are Formatted as Text

If the dates are formatted as text strings, the comparison might not work properly.

Here, we expect the result to be FALSE yet we got TRUE. That’s because both of the dates are in fact in Text format. Comparing text is done by comparing the characters from left to right, ignoring the letter case (so Excel sees that “02” is greater than “01” and returns B5>C5)

Solution – Change the Formatting to Date

Select the cells containing dates.
Click on the Formatting dropdown option.
Choose the option Short Date.

Re-enter the dates if needed. You will get the desired result.

Reason 3 – There Are Hidden Time Values in Dates

Cells that contain date values could be storing the time value as well, which doesn’t show if the cell is formatted to show the date only.

Here, we can see that even if the dates are exactly the same, we are getting FALSE as a result. Both cells contain time values included with the Date value in cell B5.