Excel Formula for Checking If a Date Is Before Another Date

Formulas & functions · Updated November 18, 2026

To check whether one date is earlier than another in Excel, use the less-than operator: with the date to test in A2 and the comparison date in B2, enter =A2<B2. To return words instead of TRUE or FALSE, use =IF(A2<B2,"Before","Not before").

The Basic Excel Formula

Excel compares dates by their underlying numeric values, so the less-than operator works for dates just as it does for numbers. A result of TRUE means the date in A2 comes before the date in B2; FALSE means it is the same date or later. This test is strict: an equal date is not considered before.

For a descriptive result, IF checks the comparison and returns one value when it is true and another when it is false. With the date to test in A2 and the cutoff date in B2, use =IF(A2<B2,"Before","Not before"). Change the words in quotation marks to suit your worksheet, such as “Early” and “On time.” The formula returns “Not before” for both a matching date and a later date, so use a different formula if you need to distinguish those cases.

To apply the test to many rows, enter the formula on the first row and fill it down. For example, if each row has a submitted date in A and a deadline in B, place the formula in C2 and copy it through the remaining rows. Excel adjusts the relative references to compare each row’s submitted date with that row’s deadline.

How Excel Compares Dates

Excel stores recognized dates as serial numbers: the whole-number portion represents a day, and a fractional portion can represent a time on that day. The cell’s date format controls how that value looks; it does not determine whether Excel can compare it. Thus, two cells can display dates in different formats and still compare correctly if both contain genuine Excel date values.

For instance, a displayed date of January 10, 2026 is earlier than January 15, 2026, so a less-than test returns TRUE. You do not need to convert either date to text or use a special date-comparison function. Comparisons also work across months and years because Excel compares the underlying serial values, not the apparent position of the day number in the calendar.

A workbook may use Excel’s 1900 or 1904 date system, which changes the serial number assigned to a displayed date. Normally, a comparison between two dates in the same workbook remains valid because both are stored using that workbook’s system. Extra care is warranted when dates are copied between workbooks that use different date systems, since a copied serial value can then represent a different calendar date.

Examples With Specific Dates

It is often clearer to build a comparison date with DATE rather than type a date inside a formula as text. With an event date in A2, compare it with March 1, 2026 by using =A2<DATE(2026,3,1). DATE takes the year, month, and day as separate numeric arguments. If A2 contains February 28, 2026, the result is TRUE; if it contains March 1 or a later date, the result is FALSE.

With the event date in A2 and the cutoff date in B2, use =IF(A2<B2,"Past cutoff","Not past cutoff") to show a label. If A2 is February 28 and B2 is March 1, the result is “Past cutoff.” If the two dates are equal, the result is “Not past cutoff,” because the formula uses <, not <=.

When equality should count as meeting the condition, change the operator. With the tested date in A2 and the reference date in B2, use =A2<=B2 to return TRUE for a date that is before or equal to the reference date. Use > for later than, >= for later than or equal to, and = to test equality. Choosing the operator carefully prevents an off-by-one-day result at a deadline.

Dates Compared With Times

If cells include times, Excel compares the complete date-and-time value, not just the calendar day. A value at 9:00 a.m. is earlier than a value at 3:00 p.m. on the same date. So, with a timestamp in A2 and another timestamp in B2, =A2<B2 returns TRUE when the first timestamp occurs earlier, even if both cells display only the date.

This can matter when a deadline is intended to include the entire day. If B2 contains midnight at the start of a date, a timestamp later on that same day is after B2. For a date-only comparison, remove the time portion with INT. With timestamps in A2 and B2, use =INT(A2)<INT(B2) to compare just their calendar days. INT rounds a positive date-time serial down to its whole-number day value.

Alternatively, make the cutoff the start of the following day when the rule is “before the day after the deadline.” With a deadline date in B2, the comparison boundary can be calculated as =B2+1; this is the next day at midnight when B2 has no time component. Be explicit about the intended rule: “earlier than the deadline instant,” “earlier calendar date,” and “any time on or before the deadline date” are not identical tests.

Handling Blank Cells and Text Dates

Blank references can produce surprising-looking comparisons. In many ordinary comparisons, Excel treats an empty cell as zero, and zero is earlier than a modern date serial. Consequently, a test may report that a blank date is before a populated comparison date. If blank means “not entered,” check for it first. With the date to test in A2 and the comparison date in B2, use =IF(A2="","Missing",IF(A2<B2,"Before","Not before")). This returns “Missing” when A2 is blank and otherwise performs the comparison.

A cell can look like a date while actually containing text. Text dates may be left-aligned by default, but alignment is not a dependable test because formatting can be changed. If Excel treats a value as text, comparisons may not behave like date comparisons, and date strings can sort in an order that differs from chronological order. Enter dates using a format Excel recognizes for the workbook’s regional settings, or convert the data to real dates before relying on the result.

For text in A2 that Excel can interpret under the computer’s regional date settings, a helper formula is =DATEVALUE(A2). It returns an Excel date serial, which you can format as a date. DATEVALUE may fail or interpret a string differently when its month-day order is ambiguous or does not match the locale. For example, a numeric-looking string such as 03/04/2026 can be read differently in different regional settings. When importing data, confirm the intended order rather than assuming the text is unambiguous.

Using a Moving Cutoff Date

A fixed date is useful for a one-time check, but some sheets need to compare each date with today. With a due date in A2, enter =A2<TODAY() to return TRUE when that date is earlier than today. TODAY updates to the current date when Excel recalculates the workbook; it does not include the current time. A date equal to today therefore returns FALSE with the strict less-than operator.

For a status label, with the due date in A2, use =IF(A2<TODAY(),"Overdue","Not overdue"). This treats a date as overdue only after its calendar date has passed. If the rule should also classify today as overdue, use =IF(A2<=TODAY(),"Due or overdue","Not yet due"). Decide how blanks should be handled before using either formula, or an empty date cell may be treated like an old date.

TODAY is a volatile function: Excel recalculates it as part of workbook recalculation, so a status can change when the date changes. That is useful for live tracking, but it means the result is not a permanent record of what the status was on a prior day. For an audit trail, store a dated value or otherwise record the status when the relevant event occurs rather than expecting a TODAY-based formula to preserve history.

Formula Results Versus Conditional Formatting

A formula is appropriate when you want a result in a cell that can be filtered, counted, or used by another formula. Conditional formatting is useful when you want to highlight the original date cells without adding a visible TRUE/FALSE result column.

To highlight dates in A2:A100 that are before today in Excel for Microsoft 365 on Windows, select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2 as the first cell of the selected range, enter =A2<TODAY(), choose a format, and confirm. The reference is relative so each row is evaluated against its own cell. On Mac, the Conditional Formatting controls are also on the Home tab; the precise dialog appearance can differ. In Excel for the web, use Home > Conditional Formatting to create a rule, though available dialog options can differ from desktop Excel.

For a fixed comparison date stored in B2, use the relative-and-absolute reference =A2<$B$2 as the conditional-formatting rule for A2:A100. The dollar signs keep the cutoff in B2 while the row reference A2 adjusts across the selected range. This highlights only the cells that meet the condition; it does not change their values.

Common Errors and Quick Checks

If a formula displays literally rather than calculating, check that the cell is not formatted as Text, and make sure the entry begins with an equals sign. Change the cell to General if needed, then re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles formula display. On Mac, the equivalent shortcut is Control+`; keyboard layouts can affect how the grave-accent key is entered.

If the answer is unexpectedly FALSE, verify the actual values rather than only their displayed formats. A hidden time can make two same-day values unequal, and a text date may not be a date serial. Widen the column or change the number format temporarily to inspect the values; use a date-and-time format if timestamps are possible. Also confirm that the references point to the intended cells and that the cutoff uses the correct year.

  • Use < for strictly earlier, and <= when a matching date should qualify.
  • Check for blank cells before comparing if blanks should not count as old dates.
  • Use INT when the rule compares calendar days and timestamps may be present.
  • Use DATE(year,month,day) for an unambiguous date constructed in a formula.

Frequently Asked Questions

What Excel formula checks if a date is before today?
With the date in A2, use =A2<TODAY(). It returns TRUE only when A2 is earlier than today; a date equal to today returns FALSE.

How do I compare two dates and return Yes or No?
With the dates in A2 and B2, use =IF(A2<B2,"Yes","No"). It returns Yes when A2 is strictly earlier than B2, including no special treatment for equal dates.

Does Excel compare dates as text?
Excel compares recognized dates as serial numbers, even when cells display them as formatted dates. Text that merely looks like a date may not compare chronologically, so convert or re-enter it as a real date first.

How can I check whether a date is before or equal to another date?
With the tested date in A2 and the comparison date in B2, use =A2<=B2. The less-than-or-equal operator includes matching dates.

Why does Excel say a blank date is earlier?
An empty cell can act like zero in a numeric comparison, and modern date serials are greater than zero. Test whether the date cell is blank first if an empty value should produce a separate result.

Related

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.