Excel Formula for Dates Before Today: Flag, Filter, Format

Formulas & functions · Updated November 19, 2026

With dates in A2:A100, use =A2<TODAY() to test whether the date in A2 is before today; Excel returns TRUE for an earlier date and FALSE for today or a future date. To highlight past dates, select the date range and create a conditional-formatting rule using that same comparison, adjusting the reference to match the range’s first cell.

What “before today” means in Excel

The comparison is strict: a date earlier than the current date qualifies, but today itself does not. This distinction matters when you are checking due dates, appointment lists, expiry dates, or task schedules. If a deadline is today, the test returns FALSE, even if that deadline is due now.

Excel stores real dates as numbers, with each whole number representing a day. A comparison such as A2<TODAY() therefore compares the date value in A2 with the date value for the current day. The cell’s display format does not determine the result: a date may appear as “June 10” or “10/06/2025” and still be compared by its underlying value.

TODAY() supplies the current date without a time. It updates when Excel recalculates the workbook, so a formula can change from FALSE to TRUE as days pass. It is not a permanently saved record of when you first entered the formula.

Test a date with a simple formula

Assume A2 contains a valid Excel date. Enter the comparison in B2 and fill it down beside the other dates. For example, if today is June 15, 2025, and A2 contains June 10, 2025, the result is TRUE. If A2 contains June 15 or June 20, the result is FALSE.

The formula returns a logical value rather than a descriptive label. That makes it useful in other formulas, filters, and conditional-formatting rules. You can also use the result directly as a decision in an IF formula.

Highlight past dates with conditional formatting

Conditional formatting changes a cell’s appearance when its value meets a rule; it does not replace the date or create a separate TRUE/FALSE result. To highlight dates in A2:A100, use the following setup. The reference A2 is relative, so Excel evaluates each row of the selected range against its own date.

  1. Select A2:A100, or the date range you want to format.
  2. On the Home tab, choose Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. With A2:A100 selected and A2 as its top-left cell, enter =AND(ISNUMBER(A2),A2<TODAY()).
  5. Choose Format, select a fill or font style, and confirm the rule.

ISNUMBER prevents blank cells and text entries from being treated as dates. Pick a visible format that still leaves the date readable. To apply the color to an entire row based on the date in column A, select the whole data range and use a reference such as $A2 in the rule; the dollar sign fixes the column while allowing the row number to change. On Mac and Excel for the web, the available controls and dialog layout can differ slightly, but conditional formatting is also available there.

Return a status, a day count, or a total

A TRUE/FALSE result is compact, but a status label may be easier for a report. With a date in A2, this formula displays “Past” for dates earlier than today and “Today or future” for today’s date and later dates. It leaves the result empty if A2 is blank:

=IF(A2="","",IF(A2<TODAY(),"Past","Today or future"))

For a date of June 10, 2025, with today assumed to be June 15, 2025, the result is Past. June 15 returns Today or future. If blank cells should instead display a message, replace the first empty-string result with the wording you want.

You can calculate how many days have elapsed since a date by subtracting it from TODAY(). With June 10, 2025 in A2 and today assumed to be June 15, 2025, this returns 5. Format the result cell as General or Number if Excel displays the answer as a date.

=TODAY()-A2

To count past dates in a list, assume A2:A5 contains June 10, June 15, June 20, 2025, and one blank cell, and suppose today is June 15, 2025. This formula returns 1: it counts dates earlier than today and excludes blanks. The greater-than-zero condition is appropriate for ordinary positive Excel date serials.

=COUNTIFS(A2:A100,"<"&TODAY(),A2:A100,">0")

Show only dates before today

If you want a separate, automatically updating list rather than highlighting cells, FILTER can return only qualifying dates. With dates in A2:A20, enter this formula in an empty area. It ignores blanks and other nonnumeric entries, and displays the fallback text if there are no past dates:

=FILTER(A2:A20,ISNUMBER(A2:A20)*(A2:A20<TODAY()),"No past dates")

If the example range contains June 10, June 15, and June 20, 2025, and today is June 15, the result contains June 10 only. Format the output cells as dates if they show serial numbers. FILTER is available in Microsoft 365 and Excel 2021 or later; in older Excel versions, use the built-in column filter or a helper column containing the TRUE/FALSE test.

Dates, times, and the current moment

TODAY() represents midnight at the start of the current day. A cell containing only today’s date compares equal to TODAY(), so it is not before today. A date-and-time value from earlier today is greater than TODAY(), not less, because it includes a time later than midnight. This is usually correct for a question about dates, but it can surprise people who are trying to find records that happened earlier today.

For a timestamp comparison against the current moment, use NOW() instead. With a date and time in A2, =A2<NOW() returns TRUE when that timestamp is earlier than the current date and time. For example, a timestamp at 9:00 a.m. returns TRUE if the current time is 3:00 p.m. on the same day; a timestamp at 5:00 p.m. returns FALSE. NOW() also updates on recalculation, and its displayed precision depends on the cell format.

If your goal is to treat any time today as “today,” comparing the calendar date rather than the timestamp may be more suitable. One approach, assuming a valid date-time value in A2, is =INT(A2)<TODAY(). INT removes the time portion for this comparison, so any time today is treated as today, not as a past date. Do not use that adjustment if the time itself determines whether an event is overdue.

Common problems and how to fix them

  • The formula is always FALSE: Check that the cell contains an actual Excel date, not text that merely looks like one. Re-enter the date using a recognized date format, or use Excel’s date-conversion tools where appropriate. Text interpretation can depend on regional date settings.
  • Blank cells get highlighted: Add ISNUMBER to a conditional-formatting rule, as shown above. A blank can behave like zero in some comparisons, and zero is earlier than today.
  • Today is highlighted unexpectedly: Check that the rule uses < rather than <=. The latter includes today. Use the inclusive comparison only if “today or earlier” is what you intend.
  • The color appears on the wrong row: In a rule applied to A2:A100, the formula should refer to A2, the range’s first cell. Check the “Applies to” range and whether dollar signs are fixing a row or column unintentionally.
  • The result does not update: TODAY() and NOW() depend on recalculation. Check Formulas > Calculation Options and choose Automatic if the workbook is set to Manual. You can also recalculate the workbook; on Windows, F9 recalculates open workbooks. On Mac, the common equivalent is Fn+F9, though keyboard behavior can depend on the Mac’s function-key settings.

Also be cautious when importing dates from another system. A value stored as text will not behave like a true date, and ambiguous entries such as 03/04/2025 can be interpreted differently under different regional settings. A consistent, explicit date format and a quick check of a few imported cells can prevent misleading results.

Choose the right method for the job

Use a direct comparison when you need a reusable TRUE/FALSE test, such as for a helper column or another formula. Use conditional formatting when the important outcome is visual scanning. Use COUNTIFS when you need a numeric total, and FILTER when you want a separate list that updates with the date. These methods all rely on the same boundary: “before today” excludes today.

For a one-time review, Excel’s ordinary filter controls may be quicker than adding formulas. For a recurring workbook, formulas and conditional formatting recalculate as the date changes. If the workbook must preserve the status as it was on a particular day, TODAY() alone is not enough: it is dynamic. Record a fixed date value separately, or save a dated snapshot, rather than expecting a live formula to retain its former result.

Frequently Asked Questions

How do I check whether a date has passed in Excel?
With a valid date in A2, use =A2<TODAY(). TRUE means the date is earlier than today; FALSE means it is today or later.

How do I include today as a past or due date?
Use an inclusive comparison. With the date in A2, =A2<=TODAY() returns TRUE for today as well as earlier dates.

Does TODAY() update automatically?
It updates when Excel recalculates the workbook. Automatic calculation is the usual setting, but a workbook set to Manual may keep an older displayed result until recalculated.

Can I highlight dates before today without a helper column?
Yes. Select the date cells, create a formula-based conditional-formatting rule, and use a relative reference to the first selected cell. Add ISNUMBER if the range may contain blanks or text.

Why does Excel say a date is in the past when the cell looks blank?
The cell may contain a zero, a formula returning zero, or a blank being included in a comparison. Use ISNUMBER to require a numeric date before testing whether it is earlier than TODAY().

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.