Excel Date Comparison: Formulas, Rules, and Fixes

With dates in A2 and B2, compare them using =A2<B2: Excel returns TRUE if A2 is earlier than B2 and FALSE otherwise. Use =A2=B2 to test whether they are equal, or replace the operator with >, <=, or >= to answer a different comparison question.

How Excel stores and compares dates

Excel does not normally store a date as a word or as a specially typed calendar object. It stores dates as serial numbers and displays those numbers using a date format. In the default 1900 date system used by most Windows workbooks, dates progress by one whole number per day: a later date has a higher serial value. The time of day, if present, is stored as a fraction of a day. For example, noon is one-half of a day.

That numeric representation is why ordinary comparison operators work for dates. A date in A2 can be less than, greater than, or equal to a date in B2 just as numbers can. The displayed format does not change the underlying value: cells showing dates as “Jan 5” and “1/5/2026” can compare equal if they contain the same date and time.

There is an important exception: text that looks like a date may not be a real date value. Imported data, pasted values, and dates written in a format Excel does not recognize in the workbook’s regional settings can remain text. Changing the cell’s number format alone does not convert text into a date serial.

Choose the comparison operator

Use the operator that matches the question. For a simple example, assume A2 contains January 15, 2026, and B2 contains January 20, 2026. The first formula below returns TRUE; the second returns FALSE.

=A2<B2 asks whether A2 is earlier than B2.
=A2>B2 asks whether A2 is later than B2.
=A2=B2 asks whether the values are exactly equal.
=A2<=B2 and =A2>=B2 include equality as well as earlier or later dates.
=A2<>B2 asks whether the values differ.

Here the assumed layout is A2 with January 15, 2026 and B2 with January 20, 2026; =A2<B2 returns TRUE. For a readable label rather than TRUE or FALSE, use an IF formula. With the same two cells and dates, =IF(A2<B2,"Before","Not before") returns “Before.”

Comparisons can also be embedded in other formulas. For example, a comparison can determine which branch an IF formula uses, or whether a row meets a condition. The comparison itself remains a logical test; Excel represents its result as TRUE or FALSE.

Compare against a specific calendar date

When comparing a cell with a fixed date, avoid typing an ambiguous date string directly into a formula. DATE makes the year, month, and day explicit. With January 15, 2026 in A2, =A2=DATE(2026,1,15) returns TRUE if A2 contains that date at midnight. DATE uses the order year, month, day, regardless of how the date is displayed in the cell.

A formula can also compare against a date stored in another cell. This is useful when a cutoff date needs to be changed without editing formulas. With a due date in A2 and a cutoff date in B2, =A2<=B2 returns TRUE when the due date is on or before the cutoff. If A2 is January 15 and B2 is January 20, 2026, the result is TRUE.

Be mindful of what “equal” means. Two cells that both display January 15, 2026 might not be equal if one includes a time such as 9:00 AM. The display may hide the time, but the underlying values still differ.

Compare dates that include times

Because time is stored as a fraction of a day, a date-time value can be later than a date at midnight on the same day. If A2 contains January 15, 2026 at 9:00 AM and B2 contains January 15, 2026 at 3:00 PM, =A2<B2 returns TRUE. But =A2=B2 returns FALSE. This is usually the correct behavior when comparing appointment times, event timestamps, or deadlines with times attached.

If the question is whether two values fall on the same calendar day, disregard the time portion with INT. With those date-time values in A2 and B2, =INT(A2)=INT(B2) returns TRUE because both values have the same whole-number date serial. INT removes the fractional time portion for this comparison; it does not change the original cells.

For a whole-day range, use a start boundary that includes midnight and an end boundary that excludes the next midnight. This avoids missing entries later in the final day. With timestamps in A2:A100 and a start date in B2 and end date in C2, a row-level test for a timestamp on or after B2 and before the day after C2 is =AND(A2>=B2,A2<C2+1). If A2 is January 20 at 4:30 PM, B2 is January 20, and C2 is January 20, the result is TRUE.

Use TODAY for changing date checks

TODAY returns the current date without a time and updates when Excel recalculates. It is useful for checks such as whether a date is in the past or whether a deadline has arrived. With a due date in A2, =A2<TODAY() returns TRUE when A2 is earlier than today, and FALSE when it is today or a future date. To count today as due or overdue, use =A2<=TODAY(). The result changes over time, so it is not a fixed historical record of the day the workbook was last checked.

When A2 contains a date and time, comparing it with TODAY has a subtle effect: TODAY represents midnight at the start of today. A timestamp later today is greater than TODAY, even though its calendar date is today. To test whether a timestamp falls today, with the timestamp in A2, use =AND(A2>=TODAY(),A2<TODAY()+1). This returns TRUE for any time today and FALSE for timestamps on other days.

Highlight dates with conditional formatting

Conditional formatting can make comparisons visible without adding a formula column. To highlight dates in A2:A100 that are earlier than today, select that range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =A2<TODAY(), choose a format, and confirm. The reference A2 is relative, so Excel adjusts it for each row. If the selected range begins somewhere else, use the top-left cell of that range in the formula.

To highlight cells that match a single cutoff date in D1, select A2:A100 and create a formula-based rule using =A2=$D$1. This assumes A2:A100 contains the dates and D1 contains the cutoff. A cell in the selected range is formatted when its value equals D1. The dollar signs keep the reference to D1 fixed while Excel applies the rule down the selection.

For a straightforward comparison between two cells, a formula cell can be easier to audit than a formatting rule; conditional formatting is most useful when visual scanning matters. On Excel for Mac, the ribbon’s Conditional Formatting commands are also under Home, though dialogs can look different. Excel for the web supports conditional formatting, but some advanced rule editing options may be more limited than in the desktop app.

Fix text dates and other common problems

If a comparison gives an unexpected result, first check whether both cells are real dates and whether either contains a time. Select the cells and look at the formula bar; changing the display to a number format can also reveal whether Excel is treating a value as a serial number. A text date may align left by default, but alignment alone is not definitive because formatting can override it.

Convert imported text: Select the affected cells and try Data > Text to Columns, then choose the appropriate date order in the final step when the source is consistently formatted. For a simple value, re-entering it in a date format Excel recognizes can also convert it.
Check regional date order: A string such as 03/04/2026 can mean March 4 or April 3 depending on the expected order. Confirm the source convention before converting; otherwise, valid-looking dates can be interpreted as the wrong day.
Look for hidden times: Display the values with a date-and-time format or compare their integer portions if the question concerns calendar days only.
Check for blanks or errors: A blank, a text value, or an error in one of the cells can make a comparison behave differently from the intended date test. Validate the source data before relying on results.
Account for workbook date systems: Excel supports 1900 and 1904 date systems. Workbooks using different systems can show a 1,462-day shift when dates are transferred between them. Check the workbook’s date-system setting if copied dates appear roughly four years and one day off.

Do not “fix” a comparison simply by changing the cell’s appearance. A date format changes how a numeric value is displayed, not whether text has become a true date or whether a hidden time exists. Correct the stored value or adjust the comparison to match the intended meaning.

Count or filter dates around a cutoff

A comparison can serve as a condition for counting records. With dates in A2:A20 and a cutoff date in B1, =COUNTIF(A2:A20,"<"&B1) counts dates earlier than the cutoff. If the range contains January 10, January 20, and January 30, and B1 contains January 25, the result is 2. The ampersand joins the comparison operator to the date value in B1, which is the syntax COUNTIF needs for a criteria expression.

For a changing cutoff, use TODAY in place of a stored date, while remembering that the result will update. With dates in A2:A20, =COUNTIF(A2:A20,"<"&TODAY()) counts dates before the current day. To include the current day, change the operator inside the quoted criterion to “<=”. COUNTIF is suitable for one condition; for counts involving multiple conditions, such as dates within a range and a matching department, COUNTIFS is designed for multiple criteria.

Frequently Asked Questions

Why does Excel say two dates are not equal when they look identical?
One cell may contain a time value that its number format hides, or one value may be text while the other is a true date serial. Inspect the underlying values and decide whether the comparison should include time.

How do I compare dates without considering the time?
Use INT on each date-time value before comparing. For values in A2 and B2, =INT(A2)=INT(B2) returns TRUE when they fall on the same calendar date, even if their times differ.

How can I tell whether a date is overdue?
With the due date in A2, compare it with TODAY. Use a strict less-than test to mark only dates before today; use less-than-or-equal if a deadline dated today should also count as due.

Can I compare dates written as text?
It is safer to convert text dates to genuine Excel dates before comparing. Text can sort or compare differently from date serials, and ambiguous strings can be interpreted incorrectly depending on regional settings.

Why does a copied date appear years off?
The source and destination workbooks may use different date systems, or a text date may have been parsed with the wrong day-month order. Check the workbook settings and the original data format before correcting values.

For reliable date comparisons, confirm that the cells contain real date values, decide whether time matters, and choose the operator that matches the business rule. Once those details are clear, Excel’s ordinary comparison operators handle most date checks directly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.