If your dates are in A2:A100 and the date to check is in D2, use =COUNTIF(A2:A100,D2) to count cells that match that date exactly. For example, if D2 contains April 2, 2026, and that date appears three times in the range, the formula returns 3; for date periods, use comparison criteria or COUNTIFS.
How COUNTIF handles dates
Excel stores genuine dates as numbers: each date corresponds to a serial value, and a time of day is stored as a fractional part of that value. The cell may display as April 2, 2026, or 4/2/26, but the underlying date value is what COUNTIF compares. The display format does not determine whether two date values match.
COUNTIF takes a range and one condition. A date cell can serve as the condition directly, while a condition such as “on or after this date” combines a comparison operator with a date value. The formula counts qualifying cells; it does not change their formatting or convert text into dates.
For the examples below, assume the cells contain real Excel dates unless a section specifically discusses text dates. The sample dates are described in month/day/year order for clarity. A workbook’s regional settings can affect how typed dates are interpreted, so check that Excel recognizes an entry as a date rather than relying on how it looks.
Count an exact date
An exact-match count is useful for tallying appointments, orders, attendance, or transactions recorded on a particular calendar day. Put the date you want to check in a separate cell so you can change the target without editing the formula. Excel can format that target cell as a date using Home > Number Format.
With dates in A2:A8 and April 2, 2026, in D2, use =COUNTIF(A2:A8,D2). If A2:A8 contains April 1, April 2, April 2, April 3, April 2, April 30, and May 1, the result is 3. The comparison is against the date value, not the way the dates are displayed.
If you enter the target date directly in a formula, be cautious about typing a date string inside quotation marks: interpretation may vary with regional settings, and COUNTIF criteria have their own text syntax. A cell reference is usually clearer and easier to update. When you need a fixed date independent of date-entry conventions, Excel’s DATE function can construct one from year, month, and day numbers.
Count dates before or after a cutoff
For a date condition that includes an operator, join the operator in quotation marks to a date value with the ampersand. The operator must be part of the criteria text, while the date can come from a cell. Use greater-than or less-than for strict comparisons; use greater-than-or-equal or less-than-or-equal when the cutoff date itself should count.
With dates in A2:A8 and March 15, 2026, in D2, use =COUNTIF(A2:A8,">"&D2) to count dates strictly later than the cutoff. For the sample range March 1, March 5, March 31, April 1, March 15, March 31, and February 28, the result is 3: March 31, April 1, and the second March 31 are later than March 15.
To count dates on or after the cutoff, change the condition to greater-than-or-equal. To count dates before it, use less-than; to include the cutoff in that count, use less-than-or-equal. Choosing the operator deliberately prevents a common off-by-one error, especially when reporting totals for a period that is supposed to include its first or last day.
Count dates within a range
COUNTIF can test only one condition at a time, so an inclusive start and end date require two conditions. Use COUNTIFS, which counts rows meeting all of its conditions. Set the start date in D2 and the end date in E2, and apply both criteria to the same date range.
With dates in A2:A8, the start date March 1, 2026, in D2, and the end date March 31, 2026, in E2, use =COUNTIFS(A2:A8,">="&D2,A2:A8,"<="&E2). For the sample dates March 1, March 5, March 31, April 1, March 15, March 31, and February 28, the result is 5. Both March 31 entries count because the end condition includes the final day.
A useful alternative is to make the end boundary exclusive: count dates on or after the start and strictly before the day after the end. This approach is especially valuable when the cells may include times, as explained below. Keep the criteria ranges the same size; COUNTIFS pairs conditions by position, and mismatched ranges can produce an error or an unintended count.
Count a month or another calendar period
To count all dates in a month, use the first day of that month as an inclusive lower boundary and the first day of the following month as an exclusive upper boundary. This avoids having to calculate the month’s final day, which may be the 28th, 29th, 30th, or 31st.
With dates in A2:A8, March 1, 2026, in D2, and April 1, 2026, in E2, use =COUNTIFS(A2:A8,">="&D2,A2:A8,"<"&E2). For the sample dates in the previous section, the result is 5 because five dates fall in March, and April 1 is excluded. The same boundary pattern works for a quarter or year: set the cells to the first day of the period and the first day after it.
A date displayed with a month name is still a number if Excel recognizes it as a date. Wildcards are designed for text matching, so a pattern that looks for “March” in a date cell is not a dependable way to count that month. Use date boundaries instead. They also make the intended period explicit and are easier to review when a workbook is shared.
When the cells include times
A timestamp contains both a date and a time. Consequently, an exact match against a date at midnight will not match a value later that same day: the underlying numbers differ by the fractional time component. This is why an exact-date COUNTIF can return zero even though the cell visibly shows the date you are counting; the time may simply be hidden by the number format.
To count every timestamp on a target day, use the day’s midnight as the lower limit and the next midnight as an exclusive upper limit. With timestamps in A2:A6 and April 2, 2026, in D2, use =COUNTIFS(A2:A6,">="&D2,A2:A6,"<"&D2+1). If the entries are April 2 at 9:00 a.m., April 2 at noon, April 2 at midnight, April 3 at 8:00 a.m., and April 1 at 11:00 p.m., the result is 3.
The exclusive next-day boundary is safer than trying to specify the last possible time on the target date. It includes every time during the day while excluding midnight at the start of the following day. The same idea applies to multi-day ranges: use an inclusive start and an exclusive boundary immediately after the final date.
Common problems and ways to check them
- Dates are stored as text. A value that looks like a date may have been imported as text. Number formatting alone does not convert text into a numeric date. Check the source data and use a consistent conversion method before counting; for imported columns, Data > Text to Columns can help when you select the correct date order during the conversion steps.
- The date includes a hidden time. Change the number format temporarily to show a time, or count a whole day using the two-boundary method. Formatting a timestamp to show only its date hides the time but does not remove it.
- The typed date is being interpreted differently. Regional settings can make entries such as 4/5/2026 ambiguous. Use a date cell that you can verify, or construct dates with DATE when building a formula. Do not assume that changing the display format repairs an incorrectly interpreted value.
- The criteria operator is not joined to the date. A condition such as greater than a cell value needs the operator as text and the cell reference joined with an ampersand. Check quotation marks and the reference if the result is unexpectedly zero.
- The range misses some rows. Confirm that the formula covers the full data area and that the condition cell is not accidentally included as a data record. For a table that grows regularly, using a structured table column can make the counted range easier to maintain.
For a quick diagnosis, select a suspect date cell and inspect the formula bar and number format, then test the formula on a small range whose contents you can count manually. If a simple exact match fails but the date appears correct, check for hidden times or text values before changing the criteria repeatedly.
COUNTIF or COUNTIFS: which should you use?
Use COUNTIF when one rule is enough: an exact date, dates later than a cutoff, or dates on or before a deadline. Use COUNTIFS when a record must satisfy multiple rules, such as being on or after a start date and before an end date. It can also combine a date condition with a separate condition, such as counting orders in a period only when their status is “Complete.”
For a daily count, COUNTIF is shorter if the cells contain date-only values. For data that might include timestamps, COUNTIFS with the start-of-day and next-day boundaries is more reliable. For a month, a pair of COUNTIFS conditions is clearer than attempting to match a month name. These methods count records; they do not group or summarize the data into a report. A PivotTable is an alternative when you want a reusable summary by date, month, or other fields.
Frequently Asked Questions
Can COUNTIF count dates in Excel?
Yes. If Excel recognizes the entries as dates, COUNTIF can count an exact date or dates meeting a single comparison condition. Use COUNTIFS for a period with both a start and end boundary.
How do I count dates in a specific month?
Use COUNTIFS with a condition greater than or equal to the first day of the month and another condition less than the first day of the next month. This avoids relying on text matching or calculating the month’s last day.
Why does COUNTIF return zero for a date that is visibly in the list?
The entry may be text, may contain a hidden time, or may not be the same underlying date as the criterion. Check the cell’s value and date interpretation, and use a whole-day boundary test if timestamps are possible.
Can COUNTIF count dates between two dates?
Not with one COUNTIF condition. Use COUNTIFS to apply both boundaries to the same range. Decide whether the start and end dates should be included, then choose inclusive or exclusive comparison operators accordingly.
Do date formats affect the count?
Display formats change how dates look, not the underlying numeric value. However, formatting cannot turn text into a real Excel date, and hiding a time does not remove it; both issues can affect matching.
Related
- Insert Image in Excel: Steps, Cell Options, and Tips
- Cannot Find Data Validation in Excel? Here’s Where
- Cannot Close Excel Because a Dialog Box Is Open? Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation Formula: Rules, Examples & FixesAn Excel data validation formula is a custom rule that controls what users can enter in selected cells.…
- Calculate Age in Excel Using Date of Birth with DATEDIFTo calculate age in Excel from a date of birth, put the birth date in A2 and enter…
- XLOOKUP Examples With Arrays in ExcelWith product names in A2:A6, prices in B2:B6, and a name to find in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found").…
- Excel Timesheet Calculator Formula: Examples and TipsFor an Excel timesheet with start time in B2, end time in C2, and an unpaid break duration…
- Sort Excel and Keep Rows Together Without Data Mix-UpsTo sort Excel data and keep each record together, select the entire data range or click inside an…
- Extract Text in an Excel Cell: Formulas and Steps That WorkTo extract part of a cell’s text, use a formula such as TEXTBEFORE for text before a separator,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.