To count dates in Excel, use COUNTIF with a date value for an exact match, or join a comparison operator to a date for a range. For example, with dates in B2:B10, =COUNTIF(B2:B10,DATE(2025,1,15)) counts entries on January 15, 2025; the key is that Excel must recognize the values as dates, not text.
How COUNTIF treats dates
Excel stores a date as a number representing a day in its date system. A time, if present, is stored as a fractional part of that number. The cell may display a friendly date such as Jan 15, 2025, but COUNTIF evaluates the underlying value rather than the way it looks on the screen.
COUNTIF counts cells in one range that meet one condition. Its condition, called the criterion, can be a number, text, comparison, or a reference to a cell. Date formulas are most reliable when they use DATE(year,month,day) or a cell containing a real Excel date. This avoids ambiguity between regional formats such as 1/5/2025, which can mean January 5 or May 1 depending on settings.
The examples below use Microsoft Excel for Microsoft 365 on Windows and assume B2:B10 contains these nine real dates: January 1, January 5, January 15 twice, January 31, February 1, February 14, February 28, and March 1, 2025. Unless stated otherwise, the dates have no time values.
Count one exact date
With dates in B2:B10, this formula counts records whose date is exactly January 15, 2025. It returns 2 for the sample data:
With the nine sample dates in B2:B10, use:
=COUNTIF(B2:B10,DATE(2025,1,15))
DATE builds a valid Excel date from year, month, and day numbers; it does not depend on how the computer writes date text. You can also put a real date in a worksheet cell, such as G2, and use that cell as the criterion. With the sample dates in B2:B10 and January 15, 2025 entered as a real date in G2, this returns 2:
=COUNTIF(B2:B10,G2)
Formatting G2 as a date is useful for people reading the sheet, but formatting alone does not turn text into a real date. If G2 contains the text “January 15, 2025,” COUNTIF may not match numeric date values in the range.
Count dates before, after, or on a boundary
For a comparison, put the operator and date together as the criterion. The operator is text, so join it to a date value with an ampersand. With the sample dates in B2:B10, this counts dates on or after January 15, 2025. It returns 6: January 15 twice, January 31, and the three February and March dates.
With dates in B2:B10, use:
=COUNTIF(B2:B10,">="&DATE(2025,1,15))
In an actual Excel formula, the criterion uses the characters >= and an ampersand; the HTML entities shown here display those characters in this article. For a formula typed into Excel, enter =COUNTIF(B2:B10,">="&DATE(2025,1,15)) with the comparison criterion quoted. Similarly, use < for dates earlier than a boundary, <= for dates through and including it, and > or >= for dates after or on it.
For clarity, a standard typed formula for the same example is =COUNTIF(B2:B10,">="&DATE(2025,1,15)). The criterion text in Excel is “>=”; the displayed HTML encoding above represents the less-than and greater-than characters. The general pattern is an opening quote, comparison operator, closing quote, ampersand, then a date value or date-producing expression.
Count a date range or a calendar month
COUNTIF accepts one criterion, so it cannot by itself express both a starting date and an ending date. Use COUNTIFS when a count must meet multiple conditions. For a date interval, the usual pattern is an inclusive start and an exclusive end: count dates at or after the first day, but before the first day after the period. That includes every date in the period without having to guess its final time value.
With the sample dates in B2:B10, the formula below counts January 2025, from January 1 inclusive up to February 1 exclusive. It returns 5:
=COUNTIFS(B2:B10,">="&DATE(2025,1,1),B2:B10,"<"&DATE(2025,2,1))
COUNTIFS pairs each condition with a range; here the same date range is tested against both limits. This method also works for quarters, years, and custom reporting periods. For a full year, set the lower bound to January 1 of that year and the upper bound to January 1 of the following year.
If E2 contains the first day of a month as a real date, the next formula counts that month without manually entering its final day. With sample dates in B2:B10 and January 1, 2025 in E2, it returns 5:
=COUNTIFS(B2:B10,">="&E2,B2:B10,"<"&EDATE(E2,1))
EDATE advances a date by a specified number of months, so the same formula can be copied for other month-start values. Check that E2 is actually the first day of the month if the formula is intended to count a whole calendar month.
Dates with times and timestamp data
A date-and-time value is not equal to the date at midnight: the time makes the stored number slightly larger. Therefore, an exact-date COUNTIF can miss entries that display the same day but include times. This commonly happens with imported transaction, attendance, or log data.
With timestamps in B2:B10 and a real date, without a time component, in G2, this COUNTIFS formula counts every timestamp occurring on that calendar day. It returns 2 if exactly two of the sample records occur on G2:
=COUNTIFS(B2:B10,">="&G2,B2:B10,"<"&G2+1)
The start includes midnight at the beginning of the chosen day; the next-day limit excludes midnight of the following day. This is more dependable than trying to list every possible time or using an end boundary such as 11:59 PM. If the data has no times, the same interval logic remains valid.
COUNTIF or COUNTIFS?
Choose COUNTIF when one condition is enough, such as counting a specific date or everything on or after a cutoff. Choose COUNTIFS when you need two date boundaries or additional criteria. For example, you might count orders in January for one salesperson by combining a date-range condition with a name condition, provided the date and name ranges cover corresponding rows.
COUNTIF does not offer a built-in “month” condition. A condition such as “January” is not a meaningful test of numeric Excel dates. Instead, use a start and end date as shown above. This approach is also easier to audit: the boundaries are visible, and the formula handles months with different numbers of days, including leap-year February.
Fix text dates and other common problems
If a formula returns zero unexpectedly, first check whether the cells contain real date values. A date-looking string imported from a CSV may remain text, and changing its number format will not convert it. One clue is alignment or behavior that differs from neighboring date cells, though neither is conclusive because formatting can be customized.
For a small range, re-entering a date or using a date-producing function can correct the value. For a larger imported column, Excel for Windows offers Data > Text to Columns; the conversion choices depend on the source format, so select the correct date order if prompted. In Microsoft 365 for Windows, check the displayed result after conversion. Excel for Mac and Excel for the web have the same core COUNTIF formula behavior, though available ribbon layouts and conversion controls can vary.
Other common causes include:
- Locale ambiguity: Use DATE or a cell with a valid date rather than relying on a typed date string whose month and day order may be interpreted differently.
- Times hidden by formatting: A cell formatted to show only a date can still contain a time. Use the one-day interval method when counting a calendar day.
- Wrong range: Confirm the formula covers the complete data and does not include an unintended header or omit new rows.
- Criteria entered as plain text: For a comparison, the operator belongs inside quotation marks and must be joined to the date value with &.
- Blank or invalid source cells: Clean imported data and verify that every intended record has a date before interpreting the count.
For a quick check, select a suspected date cell and apply a date number format from Home > Number. If changing the format produces a date display, the cell is probably numeric; if it stays as text, conversion may be needed. This is a diagnostic clue, not a guarantee, because custom formats and text that resemble dates can complicate the check.
Use a changing date criterion
A fixed DATE criterion is reproducible and easy to review. If the count should update daily, Excel’s TODAY function can supply the current date. With dates in B2:B10, this counts records dated today or later; it will change as the current date changes:
=COUNTIF(B2:B10,">="&TODAY())
TODAY returns the current date without a time. The formula recalculates when Excel recalculates the workbook, so it is useful for rolling views but not ideal when a report must preserve the exact date used to produce a past result. For an auditable report, enter the cutoff date in a cell and retain that value.
Frequently Asked Questions
Can COUNTIF count dates in a specific month?
Yes, but use COUNTIFS with a start date and the first day of the next month as an exclusive limit. This counts real dates reliably and avoids assumptions about the month’s final day.
Why does COUNTIF return zero for a date that is visibly in the range?
The displayed value may be text, may include a hidden time, or may be stored differently from the criterion. Verify that the source and criterion are real Excel dates, and use a start-to-next-day interval when timestamps are possible.
Can I type a date directly into the COUNTIF formula?
You can, but a locale-dependent date string can be ambiguous. DATE(year,month,day) or a worksheet cell containing a valid date is generally clearer and more portable.
Does COUNTIF work with dates in Excel for Mac or Excel for the web?
COUNTIF and COUNTIFS use the same formula logic in Microsoft 365 for Windows, Mac, and the web. Menu locations and data-conversion tools can differ, but the date criteria patterns remain the same.
How do I count dates on one day when the cells include times?
Use COUNTIFS with a lower bound equal to the selected date and an upper bound equal to the next day, excluding that upper bound. This captures all times during the day without requiring exact timestamp matches.
Related
- Copy an Entire Excel Spreadsheet to a New File
- Copy and Paste X Times in Excel: Repeat Cells or Rows
- Convert H:M:S to a Number in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate Names in Excel: Formulas and ExamplesTo concatenate names in Excel, join the cells with the ampersand operator and add a space between them:…
- How to Set an Absolute Cell Reference in Excel FormulasTo set an absolute cell reference in Excel, add a dollar sign before both the column letter and…
- Excel SUMIF for Text: Match and Join Text Values EasilyExcel has no SUMIF function that adds text strings, but in Microsoft 365 you can use TEXTJOIN with…
- Excel SUBSTITUTE Shortcut: Function and Replace KeysExcel has no dedicated keyboard shortcut for the SUBSTITUTE function. To replace text in cells with a formula,…
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
- VLOOKUP in Excel: Steps, Examples, and Common FixesTo use VLOOKUP in Excel, put the value you want to find in a cell, then enter a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.