Excel Count Dates Older Than Today: Formula and Fixes

Formulas & functions · Updated November 19, 2026

To count dates in Excel that are earlier than today, use =COUNTIFS(A2:A100,"<"&TODAY(),A2:A100,"<>"), replacing A2:A100 with your date range. The formula excludes blank cells and updates as the current date changes, making it a reliable choice for a typical date list.

How the Formula Works

Excel stores real dates as serial numbers, so it can compare them as values. The TODAY function returns the serial number for the current date, without a time component. In the formula, the less-than sign means “earlier than,” and the ampersand joins that comparison operator to the date returned by TODAY.

COUNTIFS counts cells only when all its criteria are met. Here, the first condition keeps dates earlier than today; the second condition, “<>“, requires a nonblank cell. That extra check is useful because a real worksheet may contain empty rows alongside dates.

If your selected range contains only valid date values and no blanks, the shorter COUNTIF version is also useful. With dates in A2:A100 and no empty cells in that range, use =COUNTIF(A2:A100,"<"&TODAY()). For mixed or incomplete lists, prefer COUNTIFS so you explicitly rule out blanks.

Try It With a Small Example

Suppose A2:A8 contains January 10, 2025; April 30, 2025; May 14, 2025; May 15, 2025; May 20, 2025; a blank cell; and June 1, 2025. If today is May 15, 2025, the formula counts January 10, April 30, and May 14, so the result is 3. May 15 is not older than today, and the future dates and blank are not counted.

For this example, with those seven entries in A2:A8, the formula is =COUNTIFS(A2:A8,"<"&TODAY(),A2:A8,"<>"). TODAY supplies the comparison date automatically; the example date is only there to make the expected result clear. In a live workbook, the result may differ because TODAY uses the current date when Excel recalculates.

Enter the formula in an empty cell outside the date list, such as B2, then press Enter. If you are copying it elsewhere, the relative range references will move with the formula. Use absolute references, such as $A$2:$A$100, if you need the formula to keep pointing to the same cells when copied.

What “Older Than Today” Means

The less-than comparison counts dates before the current calendar day, not dates on or before today. A date equal to today is excluded. If your actual requirement is to include today as well, use a less-than-or-equal comparison instead. With dates in A2:A100, use =COUNTIFS(A2:A100,"<="&TODAY(),A2:A100,"<>"). This counts valid dates from the past and today, while still excluding blank cells.

That distinction matters for due dates, attendance, and records that are reviewed once per day. “Overdue before today” commonly means a date strictly earlier than today; “due today or overdue” includes the current date. Choose the comparison sign that matches the rule you intend to apply rather than changing the range or date format.

TODAY returns a date without a time. If your cells contain date-and-time values, a timestamp from earlier today is later than midnight at the start of today and therefore is not counted as older than today. A timestamp from yesterday is counted. This is usually the right behavior when “older” means a previous calendar date.

Dates That Include Times

For a different question—counting timestamps earlier than the exact current moment—use NOW rather than TODAY. With date-and-time values in A2:A100, use =COUNTIFS(A2:A100,"<"&NOW(),A2:A100,"<>"). NOW returns the current date and time, so a timestamp from earlier today can qualify. The result is still a count of cells, not a measure of elapsed hours or days.

Use TODAY when the boundary should be the start of the current date, as it is for a daily report. Use NOW when the boundary should move throughout the day. Because both functions update during recalculation, a result can change without anyone editing the dates. Excel’s calculation mode and recalculation behavior affect when that update appears; in a normally recalculating workbook, the formula refreshes as Excel recalculates.

Blanks, Text, and Invalid Dates

A cell that looks like a date may actually contain text. For example, a date imported from a website or CSV file can be stored as characters rather than as an Excel date value. Changing the cell’s display format to Short Date does not necessarily convert text into a real date; formatting affects appearance, not the underlying value. Such entries can be missed or compared unexpectedly.

To diagnose a suspicious entry, select it and check whether its value behaves like a date in a simple calculation or sort. You can also inspect the formula bar and compare the cell’s alignment with nearby values, though alignment is only a clue because formatting can override Excel’s usual display. If the imported text uses a consistent date pattern recognized by your regional settings, DATEVALUE can convert it.

With a text date in A2 that Excel can interpret under your regional date settings, enter =DATEVALUE(A2) in B2 and fill the formula down. Check the converted results before using them in the count, especially where day and month order could be ambiguous. For example, a text value such as 04/05/2025 can mean different dates in different regional conventions. Conversion is not safe to assume until the results match the intended dates.

Also inspect formulas that display an empty string. A formula returning "" is not the same thing as a stored date, even though the cell contains a formula. The nonblank criterion in the main COUNTIFS formula helps exclude empty-looking cells, but date text and error values still deserve separate checking when a total seems wrong.

Use a Fixed Cutoff Date Instead

Sometimes a report needs to count dates before a particular cutoff that should not move each day. In that case, enter the cutoff as a real Excel date in a cell, such as D1, and reference that cell rather than using TODAY. With dates in A2:A100 and the cutoff date in D1, use =COUNTIFS(A2:A100,"<"&D1,A2:A100,"<>"). The count then stays tied to the selected cutoff until D1 changes.

This is useful for saved monthly reports, audit snapshots, and comparisons between periods. Label the cutoff cell clearly and make sure it contains a real date value. A typed date that Excel interprets according to a different regional convention can produce a valid-looking but unintended result.

Common Reasons the Count Looks Wrong

  • The result includes a date you expected to count as old: Check whether you mean strictly before today or on or before today. The first uses <; the second uses <=.
  • The result is too small: Look for dates stored as text, dates outside the selected range, or entries with an unexpected year or month. Confirm that the formula covers every row you want to include.
  • The result changes from day to day: That is expected when the formula uses TODAY. Use a fixed cutoff cell if the report must preserve a specific date.
  • The result does not update when expected: Check whether workbook calculation is set to Automatic under Formulas > Calculation Options. In manual calculation mode, formulas may wait for recalculation.
  • A date with a time behaves unexpectedly: Remember that TODAY represents midnight at the start of the current day. Use NOW only if the comparison should be against the current time.

Check the range boundaries as well: A2:A100 omits a date entered in A101. When building a recurring sheet, consider using an Excel table so new rows can be included more naturally, or update the formula’s range as the list grows. Keep both criteria ranges in COUNTIFS the same size and shape; mismatched ranges can cause an error.

Excel for Mac and Excel for the Web

The COUNTIFS, COUNTIF, TODAY, and NOW formulas described here also work in Excel for Microsoft 365 on Mac and Excel for the web. The formula syntax and comparison logic are the same. The visible menus and calculation controls can differ by platform, but the date-count formula itself does not need a Mac- or web-specific version.

In some regional settings, Excel expects semicolons between function arguments instead of commas. If a formula with commas is rejected, use the argument separator configured for your Excel installation. The examples here use commas, which is the standard syntax in many English-language Excel setups.

Frequently Asked Questions

How do I count dates before today in Excel? With dates in A2:A100, enter =COUNTIFS(A2:A100,"<"&TODAY(),A2:A100,"<>"). It counts nonblank date values earlier than the current date.

Does COUNTIF count dates from today? No. A criterion using <TODAY() excludes dates equal to today. Use <=TODAY() if you want to include today as well.

Will the formula update tomorrow? TODAY is recalculated by Excel, so the count can change as the date changes. If you need a permanent cutoff, put a fixed date in a cell and use that cell in the criteria.

How can I count dates from before today but ignore blanks? Use COUNTIFS with both a less-than-TODAY criterion and a nonblank criterion, as in the main formula. This makes the blank-cell rule explicit.

Why is a date not included in my count? It may be stored as text, outside the formula’s range, or equal to today rather than earlier. Confirm that Excel recognizes it as a date value and verify the comparison sign.

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.