There are some situations where you need to compare two dates with each other. But before you do, you need to understand how dates are stored in Excel.
When you enter a date in Excel, it doesn’t just see it as a date; it stores it as a number. For example, January 1, 1900, is stored as the number 1, and January 2, 1900, is the number 2. If you type in December 31, 2020, Excel sees it as 44196. But to show it as a number, you need to apply the format.
Here’s one important part: if you want to compare two dates, they must be valid according to Excel’s date system.
You can compare dates using a simple and quick formula that returns TRUE or FALSE. You can also use the IF function to get the custom message in the result while comparing two dates. The following example shows two different dates in cells A1 and B1.
You can use the following steps:
- First, enter the “=” equal sign in cell C1.
- Now refer to cell A1, where you have the first date.
- Next, enter the “=” equal sign again.
- After that, refer to cell B1, where you have the second date.
- In the end, hit enter.
=A1=B1Even though you see a date here where you compare them, Excel works with simple numbers behind it.
Comparision Operators you can use to Compare Dates
To compare dates in Excel, you can use different comparison operators:
- Equal to (A1=B1) – Checks if the date in one cell is the same as in another. If the values are equal, the condition is TRUE; otherwise, it is FALSE.
- Not equal to (A1<>B1) – Checks if the date in one cell differs from the date in another. If the values are not equal, the condition is TRUE; otherwise, it is FALSE.
- Greater than (A1>B1) – Checks if the date in one cell is larger than in another. If the first value is greater, the condition is TRUE; otherwise, it is FALSE.
- Less than (A1<B1) – Check if the date in one cell is smaller than in another. If the first value is less, the condition is true; otherwise, it is false.
- Greater than or equal to (A1>=B1) – Checks if the date in one cell is larger than or equal to in another cell. If the first value is greater or equal, the condition is true; otherwise, it is false.
- Less than or equal to (A1<=B1) – Checks if the date in one cell is smaller than or equal to in another cell. If the first value is less or equal, the condition is true; otherwise, it is false.
Compare IF a Date is Greater than Another Date
In the following example, we compare the first date with the second if the first date is greater than the second date.
In the formula, we used the greater than (>) operator.
If somehow you have dates with different date formats, you can still compare them with each other; the only thing that you need to take is that it’s a valid date.
Compare a Date with Today’s Date
In the same way, if you want to compare a date with today’s date, you can use the TODAY function. We used the TODAY function in the following example and compared it to another date.
Compare Two Dates using the IF Statement
You can also use IF to compare two dates in Excel to get a meaningful message about the comparison in the result cell. Below are a few examples to compare two dates using different comparison operators.
- The first formula checks if the date in cell A1 is the same as in cell B1. If they are equal, it returns “Dates are equal”; otherwise, it returns “Dates are not equal.”
- The second formula checks if the date in cell A1 is later than in cell B1. If A1 is later, it returns “A1 is later”; otherwise, it returns “A1 is not later.”
- The third formula checks if the date in cell A1 is earlier than in cell B1. If A1 is earlier, it returns “A1 is earlier”; otherwise, it returns “A1 is not earlier.”
Compare Dates Based on Years, Months, or Days
If you want to compare two dates but only the year, month, or days between them, then you need to write a formula differently.
- The first formula checks if the year part of the date in cell A1 is the same as in cell B1. If the years are equal, it returns “Years are equal”; otherwise, it returns “Years are not equal.”
- The second formula checks if the year of the date in cell A1 is greater than in cell B1. If the year of A1 is later, it returns “A1 year is later”; otherwise, it returns “A1 year is not later.”
- The third formula checks if the year of the date in cell A1 is less than the year of the date in cell B1. If the year of A1 is earlier, it returns “A1 year is earlier”; otherwise, it returns “A1 year is not earlier.”
And in the same ways we have formula to compare dates based on months and days:
- The first formula checks if the month of the date in cell A1 is the same as in cell B1. If the months are equal, it returns “Months are equal”; otherwise, it returns “Months are not equal.”
- The second formula checks if the month of the date in cell A1 is greater than in cell B1. If the month of A1 is later, it returns “A1 month is later”; otherwise, it returns “A1 month is not later.”
- The third formula checks if the month of the date in cell A1 is less than in cell B1. If the month of A1 is earlier, it returns “A1 month is earlier”; otherwise, it returns “A1 month is not earlier.”
- The first formula checks if the day part of the date in cell A1 is the same as the day part of the date in cell B1. If the days are equal, it returns “Days are equal”; otherwise, it returns “Days are not equal.”
- The second formula checks if the date in cell A1 is greater than in cell B1. If the day of A1 is later, it returns “A1 day is later”; otherwise, it returns “A1 day is not later.”
- The third formula checks if the date in cell A1 is less than the date in cell B1. If the day of A1 is earlier, it returns “A1 day is earlier”; otherwise, it returns “A1 day is not earlier.”
Compare Dates where you have time along with Dates
If you have two dates where you have time along with the dates, you can still compare them, but all the formulas that you have learned above.
But the problem is when you have two same dates with different time values, in that case, Excel will show you that date as different. For this you can use the function, INT to make it work around.