If you want to check whether two dates are the same, or whether one date comes before or after another, a simple formula can do it.
But one area that often troubles Microsoft Excel users is working with dates. This is mostly because dates can be formatted in many different ways in Excel.
So while you may see two dates in Excel and think those are the same, there is a possibility that these might be different values in the back end.
Or vice versa, you may think two cells have different dates, and it may be the same.
In this tutorial, I will show you a few techniques that you can use to compare dates in Excel. So let’s get started!
Follow along with the example file
Compare Dates in Excel.xlsx
How Does Excel Store Dates in the Cells?
Before I get into how you can compare two dates in Excel, let me first explain how date and time values are stored in Excel.
This is important, so if you don’t know this already, don’t skip this section (I will keep it short).
Dates are stored as whole numbers in the backend in Excel, and time values are stored as decimal values.
The dates in Excel start from 01 Jan 1900, which means that the value 1, when formatted as a date, would show you 01-01-1900 as the date in the cell in Excel.
Similarly, 44562 would represent 01 Jan 2022 (which means it’s day number 44562, counting 01 Jan 1900 as day 1).
Similarly, time is stored as decimal values, where 0.5 would represent 12 hours and 0.75 would represent 18 hours.
So if I have the value 44562.5 in a cell, this would represent 01 Jan 2022 at 12:00 PM.
In a nutshell, dates and time values are stored as numbers in the backend, that are formatted to show as dates and time.
This allows users to use these dates and times in calculations.
This is also the reason that not all date formats are acceptable formats in Excel. If Excel doesn’t recognize what you type as a date, it keeps it as a text string.
Which formats Excel recognizes depends on your computer’s regional date settings. For example, with US settings, 01.01.2022 stays as text, while Jan 01, 2022 becomes a proper date.
Compare Two Dates in Excel (Check If They’re the Same)
Now that we have a better understanding of how dates and time values are handled in the Excel backend, let’s see how to compare two dates in Excel.
Below I have store order dates in column B and warehouse order dates in column C, and I want to check whether they match in column D.
This can be done using a simple equal-to operator.
Here is the formula:
=B2:B11=C2:C11
Enter this formula in cell D2, and it spills down automatically for all the rows.
The above formula would return TRUE if the compared dates are the same, and FALSE if they are not.
Since dates are stored as numeric values, when we use this formula, Excel simply checks whether the date numeric value is the same or not.
Comparing dates in Excel is just like comparing two numbers.
Spilling formulas like this one work in Microsoft 365 and Excel 2021 or later.
In older versions of Excel, use =B2=C2 in cell D2 and copy it down.
A few important things you must know when comparing dates:
- Dates can be in two different formats and yet be equal, as the backend numeric values of these dates are the same. In our example, rows #3, #6, and #11 show the warehouse date in a different format, but the result is still TRUE.
- Dates that look exactly the same can be different. In the above example, look at row #5 and #7. The date looks the same, but the result says FALSE. This happens when the date has a time part as well but it’s formatted to only show the date. In this case, the dates in column C also have some time added to it (but it doesn’t show in the cell). I’ll show you how to handle this in the Compare Dates That Have Time Values section below.
- In case you have a date entered as a string, you won’t be able to compare these (your dates need to be in an acceptable date format). To check whether a cell has a real date, use
=ISNUMBER(B2). It returns TRUE for a real date and FALSE for a date stored as text. If you have text dates, convert them to proper dates first.
Also read: How to Change Date Format In Excel?
Compare Dates Using IF Formula (Greater Than / Less Than)
While a head-on comparison with an equal-to operator works fine, your comparison could be more meaningful when you use an IF formula.
Below I have report due dates in column B and submission dates in column C, and I want to show whether each report was on time in column D.
Below is the formula that will do this:
=IF(C2:C11<=B2:B11,"In Time","Delayed")
Enter this formula in cell D2, and it spills down automatically.
The above formula compares the two dates using the less than or equal to operator. If the submission date is on or before the due date, it shows ‘In Time’, else it shows ‘Delayed’.
You can also flip the check and use the greater than operator. =IF(C2:C11>B2:B11,"Delayed","In Time") gives you the same result.
In older versions of Excel, use =IF(C2<=B2,"In Time","Delayed") in cell D2 and copy it down.
You can also check for more than two outcomes.
Let’s say you give a grace period of 5 days. A report submitted on or before the due date is ‘In Time’, and one submitted up to 5 days late is ‘Grace’.
Anything submitted more than 5 days after the due date is ‘Delayed’.
Below is the formula that will do this:
=IFS(C2:C11-B2:B11<=0,"In Time",C2:C11-B2:B11<=5,"Grace",TRUE,"Delayed")
Enter this formula in cell E2, and it spills down automatically.
The IFS function checks the conditions in order and returns the value for the first one that’s TRUE.
The last condition is simply TRUE, which works as a catch-all. So anything more than 5 days late shows ‘Delayed’.
Note that I have subtracted the dates from each other, which is possible as dates are stored as whole numbers in the backend.
So when I subtract one date from another, it gives me the total number of days between these two dates.
The IFS function is available in Excel 2019 and later. In Excel 2019, use C2 and B2 instead of the ranges, and copy the formula down.
You can do more with the IF formula (such as nesting multiple IF statements in the same formula). If you’re using an older version, use this instead:
=IF(C2-B2<=0,"In Time",IF(C2-B2<=5,"Grace","Delayed"))
The above formula uses two IF formulas (called nested IF formulas), as we need to check for 3 conditions. Enter it in cell E2 and copy it down.
Also read: Calculate Number Of Days Between Two Dates Excel
Compare a Date With a Specific Date
So far, I’ve compared dates in two columns. But many times, you need to compare each date with one fixed date, such as a deadline or the day a price changes.
Below I have subscribers and their subscription dates, and I want to show which price each gets in column C.
The price went up on 1 March 2026, and anyone who subscribed before that keeps the old price.
Here is the formula that will do this:
=IF(B2:B11<DATE(2026,3,1),"Old Price","New Price")
Enter this formula in cell C2, and it spills down automatically.
The DATE function builds a proper date from the year, month, and day (in that order). So DATE(2026,3,1) gives us 1 March 2026.
Note that Olivia subscribed exactly on 3/1/2026, and she gets the new price. That’s because the less than operator only picks dates before the cutoff.
If you want the cutoff day itself to count, use the less than or equal to operator (<=) instead.
Important: If you type the date in double quotes, as in B2:B11<"3/1/2026", Excel compares a number with text, so every row shows Old Price.
Keep the cutoff date inside the DATE function, as in the formula above.
If the cutoff date is in a cell (say E1), you can refer to that cell instead, as in =IF(B2:B11<E1,"Old Price","New Price"). This makes it easy to change the date later.
In older versions of Excel, use =IF(B2<DATE(2026,3,1),"Old Price","New Price") in cell C2 and copy it down.
Also read: Check IF a Date is Between Two Given Dates in Excel
Compare a Date With Today’s Date
If you want to compare dates with the current date, you can use the TODAY function instead of typing today’s date in the formula.
Below I have items and their expiry dates, and I want to mark expired items in column C.
Here is the formula that will do this:
=IF(B2:B11<TODAY(),"Expired","Active")
Enter this formula in cell C2, and it spills down automatically.
The TODAY function returns the current date, so any expiry date before today shows ‘Expired’.
An item that expires today still shows ‘Active’, as the formula only picks dates before today.
The TODAY function is a volatile function, so it recalculates every time you open the file. This keeps the status up to date on its own.
It also means that your results may be different from what you see in the screenshot above.
In older versions of Excel, use =IF(B2<TODAY(),"Expired","Active") in cell C2 and copy it down.
If you want to color these dates instead of getting a text result, see how to highlight dates before today in Excel.
Compare Dates That Have Time Values
As I mentioned earlier, dates are stored as whole numbers, and time is stored as a decimal number in Excel.
Many times, people format their cells to only show the date and hide the time part.
Below is the same order dataset from earlier. This time, I’ve formatted cells C5 and C7 to show the time as well, so you can see the time part that was hidden before.
This can lead to confusion when comparing dates in Excel. For example, when I use the equal-to operator to compare the dates in the two columns, I get unexpected results.
=B2:B11=C2:C11
In the above, I get a mismatch in cells D5 and D7, while the dates are the same.
In such a scenario, you can use the INT formula to make sure you’re comparing only the day part of the date and the time part is ignored.
Below is the formula that will give us the right result:
=INT(B2:B11)=INT(C2:C11)
Enter this formula in cell E2, and it spills down automatically.
The INT part of the formula makes sure that only the completed days are considered while comparing the dates, and the decimal part is ignored.
In older versions of Excel, use =INT(B2)=INT(C2) in cell E2 and copy it down.
The same trick works with the other comparisons. For example, if a submission date has a time, INT(C2)<=B2 still counts a report submitted at 5 PM on the due date as on time.
Pro Tip: While this is an uncommon scenario, it is always a good idea to check the cell format when working with dates.
I have often found that downloads from several databases that contain date and time values both have cells formatted to only show the date part.
If you want to get rid of the time part for good, see how to remove time from date in Excel.
Operators You Can Use When Comparing Dates in Excel
And finally, below are some operators you can use when comparing dates in Excel:
- Equal to (=) – both dates are the same
- Greater Than (>) – the first date is later than the second
- Less Than (<) – the first date is earlier than the second
- Greater Than or Equal to (>=) – the first date is the same or later
- Less Than or Equal to (<=) – the first date is the same or earlier
- Not Equal to (<>) – the dates are different
In this tutorial, I covered how to compare dates in Excel using simple operators and the IF and IFS functions, including how to compare a date with a specific date or today’s date.
I also covered how to handle comparing dates when you have the time value as a part of it.
I hope you found this tutorial useful!
Other Excel tutorials you may also like:
- How to Compare Two Excel Sheets (for differences)
- How to Compare Text in Excel (Easy Formulas)
- How to Compare Two Columns in Excel (for matches & differences)
- How to Convert Serial Numbers to Dates in Excel
- How to SUM values between two dates (using SUMIFS formula)
- How to Stop Excel from Changing Numbers to Dates Automatically
- Get Day Name from Date in Excel (Easy Formulas)
- How to Add Months to Date in Excel (Easy Formula)
- Combine Date and Time in Excel (Easy Formula)
- How To Convert Date To Serial Number In Excel
- Calculate Time in Excel (Time Difference, Hours Worked, Add/ Subtract)
