Compare Dates with Specific Date in Excel: How-To

Updated 6 November 2026 · Standard formula, shown below

To compare dates with a specific date in Excel, you use comparison operators like =, >, <, >=, <=, or <>, often combined with the IF function to return custom results. For example, the formula =IF(A2>DATE(2024,1,1),"After","Before") tells you whether the date in cell A2 is after January 1, 2024.

Why Date Comparison Matters

Comparing dates against a fixed reference point is a core task in spreadsheets. You might need to flag overdue invoices, track project milestones, filter records created after a certain day, or check if a warranty is still active. Excel stores dates as serial numbers—each day is a whole number counting from January 1, 1900 (for Windows) or January 1, 1904 (for Mac). This means comparisons are simple numeric checks under the hood. Understanding this helps you avoid common pitfalls like text-formatted dates or time-of-day interference.

How Date Comparison Works in Excel

When you type a date into a cell, Excel converts it to a serial number. For example, January 1, 2024, is stored as 45292. When you compare two dates, Excel compares these numbers. The operators work exactly as they do with numbers:

= – equal to
<> – not equal to
> – greater than (later date)
< – less than (earlier date)
>= – greater than or equal to
<= – less than or equal to

To reference a specific date inside a formula, use the DATE function: DATE(year, month, day). This is safer than typing a date as text, because text dates can be misinterpreted depending on your system’s regional settings.

Using IF with Date Comparisons

The most common pattern is to return a label based on the comparison. For example, to check if a deadline in cell B2 is past a cutoff date (say, December 31, 2023):

=IF(B2>DATE(2023,12,31),"Overdue","On time")

You can also compare two cells directly: =IF(A2>B2,"A later","B later or equal"). For more complex logic, nest multiple IFs or use IFS (available in newer Excel versions). For instance, to categorize dates as “Past”, “Today”, or “Future”:

=IF(A2<TODAY(),"Past",IF(A2=TODAY(),"Today","Future"))

Comparing Dates with TODAY() and NOW()

Often you want to compare against the current date rather than a fixed one. The TODAY() function returns today’s date (no time). The NOW() function returns the current date and time. If you compare a date cell that includes a time with TODAY(), the result may be unexpected because any time after midnight makes the serial number slightly larger than the integer for today. For example, if A1 contains 2024-05-15 14:30, then =A1>TODAY() returns TRUE even though the date is today. To ignore time, wrap the date cell in INT(): =INT(A1)>TODAY().

Common Mistakes and How to Avoid Them

Text-formatted dates: If a cell contains “2024-01-01” as text, comparisons will be alphabetical, not chronological. Check the cell’s format—if it’s left-aligned, it’s probably text. Convert using DATEVALUE or the Text to Columns wizard.
Regional date formats: Typing 1/2/2024 might be January 2 or February 1 depending on your locale. Always use DATE() in formulas to avoid ambiguity.
Time hidden in dates: As mentioned, times can cause equality checks to fail. Use INT() to strip time.
Using >= vs >: Decide whether the cutoff date itself should be included. For “on or after”, use >=; for “strictly after”, use >.

Using Conditional Formatting to Highlight Dates

Instead of writing formulas in cells, you can visually flag dates that meet a condition. Select your date range, go to Home → Conditional Formatting → New Rule → Use a formula. Enter a formula like =A2>DATE(2024,6,30) (adjust the first cell reference) and set a fill color. This is useful for dashboards or lists where you want immediate visual cues without extra columns.

Comparing Dates with Other Date Functions

Beyond basic operators, Excel offers functions that compare dates in specific ways:

DATEDIF(start, end, "unit") – returns the difference in days, months, or years. For example, =DATEDIF(A2,DATE(2024,1,1),"d") gives the number of days between the two dates.
EDATE(start, months) – shifts a date by a number of months, useful for comparing anniversary dates.
EOMONTH(start, months) – returns the last day of a month, handy for monthly cutoffs.
NETWORKDAYS(start, end) – counts working days between two dates, ignoring weekends and optionally holidays.

These functions help when you need more than a simple before/after answer.

Frequently Asked Questions

Why does my date comparison return FALSE when the dates look identical?

This usually happens because one cell contains a time component, even if it’s not displayed. For example, 2024-01-01 00:00:00 is stored as 45292, but 2024-01-01 12:00 is 45292.5. The two are not equal. Use INT() to remove the time from both cells before comparing.

How do I compare a date to a specific date without typing it in a cell?

Use the DATE function inside your formula, like =A2>DATE(2024,3,15). This avoids issues with regional date formats and keeps the formula self-contained.

Can I compare dates that are stored as text?

Yes, but you should convert them first. Use DATEVALUE to turn a text date into a serial number, then compare. For example: =DATEVALUE(A2)>DATE(2024,1,1). If the text isn’t a recognized format, you’ll get a #VALUE! error.

What is the difference between TODAY() and NOW() in comparisons?

TODAY() returns only the date (serial number without fraction), while NOW() includes the current time. If you compare a date cell that has a time, use TODAY() and strip the time from the cell, or use NOW() and accept that only dates with times after the current moment will be considered “later”.

How do I count how many dates are after a specific date?

Use COUNTIF with a criteria like ">"&DATE(2024,1,1). For example: =COUNTIF(A2:A100,">"&DATE(2024,1,1)) counts all cells in A2:A100 that are after January 1, 2024. Remember to use the ampersand to concatenate the operator with the date.

Does Excel compare dates differently on Mac vs Windows?

Yes, the default date system differs. Windows uses the 1900 date system, while Mac can use the 1904 system. This affects serial numbers for dates before March 1, 1900, but for modern dates the difference is just a constant offset. If you share files between platforms, dates may appear shifted by four years. Check your workbook settings under File → Options → Advanced (Windows) or Excel → Preferences (Mac) to align them.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.