To count dates in Excel that are strictly earlier than today, use =COUNTIF(A2:A100,"<"&TODAY()), replacing A2:A100 with your date range. TODAY updates automatically, and the less-than sign means a date equal to today is not included.
How the COUNTIF formula works
COUNTIF counts cells in a range that meet one condition. In this formula, the condition is built by joining the text operator “<” to the date value returned by TODAY(). Excel then compares each cell with today’s date and counts values that are earlier.
The quotation marks around the less-than sign matter: comparison operators used as criteria in COUNTIF are written as text. The ampersand joins that text to the date value. TODAY() supplies the current date as an Excel date serial, rather than as a word or a formatted display string. You do not need to type today’s date into the formula.
For example, if A2:A100 contains appointment dates, the formula returns the number of appointments dated before today. Empty cells are not dates and are not part of the intended count. A cell containing today’s date is excluded because the test is strictly less than, not less than or equal to.
Enter the formula and check the result
- Put your dates in one column, such as A2:A100. Include a header in A1 if useful, but leave the header outside the range.
- Select the cell where you want the answer to appear, such as B1.
- Enter
=COUNTIF(A2:A100,"<"&TODAY())and press Enter. - Format the result cell as General or Number if it displays unexpectedly. The result is a count, not a date.
Here is a small illustration using the current date of November 3, 2026. Assume A2:A7 contains November 1, November 2, November 3 at midnight, November 3 at 2:00 p.m., November 4, and a blank. With those dates in A2:A7, =COUNTIF(A2:A7,"<"&TODAY()) returns 2: only November 1 and November 2 are earlier than November 3. The two November 3 entries and November 4 are not less than today.
The exact answer changes as the calendar advances because TODAY is dynamic. When the workbook recalculates on a later date, dates that were in the future may become past dates and the count can increase without editing the formula.
What counts as a date in Excel
Excel stores ordinary dates as numbers called serial values. The number represents a day on Excel’s date system; a time, when present, is stored as a fractional part of a day. Formatting changes how the value looks in a cell, but it does not change the underlying value used in the comparison.
This is why a real date can display as November 2, 2026, while Excel compares its numeric value with TODAY(). A value merely typed or imported as text may look exactly like a date but fail the comparison. Changing the cell format to Date does not necessarily convert text into a real date; formatting affects display, not the stored type.
To check a suspicious cell, select it and inspect the formula bar and alignment. General alignment is only a clue, not proof, because alignment can be changed manually. You can also test one cell with ISNUMBER, or use the sort order as a diagnostic: genuine date serials sort as numbers, while text dates may sort separately. If a source system imported dates as text, convert the source values before trusting the count.
Dates with times and whether today is included
A cell can contain both a date and a time, even if its number format displays only the date. TODAY() represents today at midnight. Consequently, the strict formula excludes every timestamp from today: a time later today is greater than today’s midnight, not less than it.
If your question is instead “How many dates are today or earlier?”, and the range may include times, use a boundary just before tomorrow. With date values or timestamps in A2:A100, =COUNTIFS(A2:A100,">0",A2:A100,"<"&TODAY()+1) counts positive date serials earlier than tomorrow. The first condition excludes blanks and non-date text; the second includes all times today as well as earlier dates. In the November 3 example above, applied to A2:A7, it returns 4: two dates before today and both November 3 timestamps.
If the cells contain dates only, without times, another way to include today is to use a less-than-or-equal condition. Be cautious with that approach when timestamps are possible: comparing a timestamp with TODAY() only includes a value exactly at midnight today, not later times on the same day. The tomorrow boundary avoids that common error.
Use a chosen cutoff date instead
Sometimes “today” is not the right reference. A report may need a fixed as-of date, a selected deadline, or a date supplied by a manager. Put that cutoff date in a cell, for example D1, and use it as the comparison value. With dates in A2:A100 and a real Excel cutoff date in D1, enter =COUNTIF(A2:A100,"<"&D1). This returns the number of dates before the value in D1, while excluding a date equal to D1.
A cell reference makes the report easier to review: change D1 and the result recalculates. It also makes historical reporting possible. TODAY is convenient for a live “past due as of now” figure, but it is not a fixed record of what the count was on an earlier day. For a saved snapshot, use an explicit cutoff date and preserve that value with the report.
Common problems and how to fix them
- The result is zero, but old-looking dates are visible. Those entries may be text rather than date serials. Convert them to real dates, then recalculate. Do not assume applying a date format alone fixes the underlying value.
- The count is too high. Check that the range does not include unrelated numeric data. Excel compares numbers, and an ordinary number lower than today’s date serial can satisfy the criterion even though it is not a calendar date.
- Today’s entries seem to be missing. That is correct for a strict “less than today” count. To include all of today when timestamps may exist, compare with tomorrow using the COUNTIFS formula above.
- The answer does not change when the day changes. TODAY recalculates with the workbook. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and choose Automatic if the workbook is set to Manual. Press F9 to recalculate formulas when needed.
- The formula shows an error after pasting. In an English-language Excel installation, the examples use commas between arguments. Some regional settings use semicolons instead. Use the separator expected by your installation and check that the quotation marks and parentheses are paired.
- Dates seem one day off. Review the original values and date interpretation. Imported dates can be interpreted differently depending on day/month order, and timestamps may be hidden by the cell’s number format. Temporarily display the full date and time to inspect what Excel actually stores.
On Excel for Mac and Excel for the web, COUNTIF and TODAY work the same way, and the formulas above are unchanged. Ribbon locations and keyboard shortcuts can differ between platforms, but no special add-in or newer function is required for this count.
COUNTIF versus filtering the date column
COUNTIF is a good choice when you want one live total in a worksheet or dashboard. It updates as dates are edited, and the formula is compact. It also keeps the answer separate from the rows being counted, which is useful when the full list must remain visible.
Filtering is useful when you need to inspect which records are earlier than today rather than just count them. In Excel for Microsoft 365 on Windows, select the data and use Data > Filter; open the date column’s filter menu and choose a date condition that is before the current date, if available in the displayed date filters. You can then review the visible records and, depending on the table or range, see a count in Excel’s status bar after selecting the relevant cells. A filter is interactive and can hide rows, so it is not a substitute for a permanent formula total.
For a straightforward one-condition count, COUNTIF is usually the simplest approach. If the task later needs several criteria—for example, dates before today and a matching department—COUNTIFS is designed for multiple conditions. Keep the criteria ranges the same size and make sure each condition describes the intended data rather than relying on cell appearance alone.
Frequently Asked Questions
How do I count dates before today in Excel?
With dates in A2:A100, enter =COUNTIF(A2:A100,"<"&TODAY()). It counts dates strictly earlier than today and updates as TODAY recalculates.
Does COUNTIF include today in a less-than-today count?
No. The less-than operator excludes a value equal to today. If you want to include today, use a less-than-or-equal condition for date-only values, or compare against tomorrow when the range may contain times.
Will this formula count blank cells?
Blank cells are not date entries and are not counted as valid dates by the intended comparison. If your range contains other numeric values, however, those numbers may satisfy a date-serial comparison, so keep the range limited to the date column or add suitable criteria.
Why does my formula return zero for dates that look old?
The displayed values may be text, or the dates may have been imported using a different day/month order than expected. Convert them to real Excel dates and verify a few sample cells before relying on the total.
How can I count dates before a date I choose?
Enter a real cutoff date in D1. With the date list in A2:A100, use =COUNTIF(A2:A100,"<"&D1). The comparison excludes dates equal to the cutoff.
Choosing the right version of the count
Use COUNTIF with TODAY when the requirement is exactly “strictly before today” and the range contains real Excel dates. Choose a tomorrow boundary when “today or earlier” must include every timestamp from today. Use a cutoff cell when the reporting date should be controlled or preserved. Whichever option fits, verify that the source cells are genuine dates and that the range contains only the records you intend to count; those two checks prevent most misleading results.
Related
- Contact List Format in Excel: Columns, Setup, and Tips
- Contact List Excel Sheet: How to Build and Use One
- Contact List Excel Spreadsheet: Create and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
- Excel IRR Monthly: Formula, Setup, and Common ErrorsTo calculate a monthly internal rate of return in Excel, put cash flows in chronological order at equal…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- First Quartile in Excel: Find It With a Simple FormulaTo find the first quartile (Q1) in Excel, use =QUARTILE.INC(A2:A9,1) when your numeric data is in A2:A9. The…
- Find a Name in Excel with a FormulaTo check whether a name appears in an Excel list, use COUNTIF; to return information associated with a…
- Power Query Lookup: How to Match Data Across TablesIn Excel, a Power Query lookup is usually a merge: open the Power Query Editor, choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.