COUNTIF Excel: Count Dates by Day, Month, or Range

Formulas & functions · Updated November 19, 2026

Yes. Excel’s COUNTIF function can count dates that match a specific day or a condition such as before or after a date; use COUNTIFS when you need a date range. For example, with dates in A2:A8, =COUNTIF(A2:A8,DATE(2025,3,8)) counts cells equal to March 8, 2025.

How COUNTIF recognizes dates

Excel stores genuine dates as serial numbers, then displays those numbers in a date format. COUNTIF compares the underlying values, not the way they look on screen. That is why a date displayed as “Mar 8, 2025” can match a criterion made with DATE, even if another cell displays the same date as “3/8/25.”

COUNTIF takes two arguments: the cells to examine and a criterion that defines what to count. A date criterion can be a date value, a comparison such as greater than a date, or a text string that combines a comparison operator with a date. The DATE function is useful because it builds an unambiguous date from year, month, and day numbers, rather than relying on regional date-entry conventions.

For the examples below, assume A2:A8 contains March 1, March 8, March 8, March 15, April 1, April 8, and one blank cell, all in 2025. These are real Excel dates, not text that merely looks like dates. The examples give results for that sample range so you can check whether your own formula is behaving as expected.

Count cells equal to one exact date

To count a particular calendar date, use COUNTIF with DATE as its criterion. With the sample dates in A2:A8, =COUNTIF(A2:A8,DATE(2025,3,8)) returns 2, because March 8 appears twice. This is usually clearer and safer than typing a date inside quotation marks: DATE(2025,3,8) is interpreted as a date value regardless of whether the computer normally displays dates month-first or day-first.

If the date to count is already entered in a worksheet cell, that cell can be the criterion instead. For instance, if D2 contains a real Excel date, use =COUNTIF(A2:A8,D2). With D2 set to March 8, 2025 and the sample range above, the result is 2. Keep the criterion cell as a real date value; a text entry that resembles a date may not compare as intended.

Exact matching means equal stored values. This distinction matters when the source cells include times as well as dates. A cell containing March 8 at 9:00 a.m. is not numerically equal to midnight at the start of March 8, even if its number format hides the time. For that situation, use a two-boundary range, described below.

Count dates before or after a cutoff

For a cutoff, join the comparison operator to the date value with an ampersand. With the sample dates in A2:A8, =COUNTIF(A2:A8,"<"&DATE(2025,3,8)) returns 1, counting only March 1. To count dates on or before March 8, change the operator to <=. To count dates strictly later than March 8, use >; to include March 8 itself, use >=.

The operator belongs inside quotation marks, while the date can remain a DATE expression outside them. Excel joins the two pieces to form the criterion. A common mistake is to write a comparison operator outside the quoted criterion or omit the ampersand; either can produce an invalid formula or an unintended comparison.

For a criterion based on a cell, suppose D2 holds the cutoff date. With dates in A2:A8, =COUNTIF(A2:A8,">="&D2) counts dates on or after D2. If D2 is March 8, 2025, the sample returns 5: the two March 8 values, March 15, April 1, and April 8. A blank in D2 does not make a meaningful date cutoff, so check that the criterion cell contains the intended date.

Count dates within a range

COUNTIF can apply one condition at a time. To count dates between a start and end date, including both endpoints, use COUNTIFS with two conditions on the same date range. With the sample data in A2:A8, =COUNTIFS(A2:A8,">="&DATE(2025,3,1),A2:A8,"<="&DATE(2025,3,31)) returns 4: the four March entries. COUNTIFS counts a row only when it satisfies every supplied condition.

For months, an exclusive upper boundary is often preferable to typing the last day. The criterion “less than April 1” includes all dates in March and avoids needing to determine how many days a month has. With dates in A2:A8, =COUNTIFS(A2:A8,">="&DATE(2025,3,1),A2:A8,"<"&DATE(2025,4,1)) also returns 4. This start-inclusive, next-period-exclusive pattern is reliable for months, years, and other calendar periods.

To make a monthly count reusable, put any date in the month you want to report in D2. With dates in A2:A8 and D2 set to March 15, 2025, =COUNTIFS(A2:A8,">="&EOMONTH(D2,-1)+1,A2:A8,"<"&EOMONTH(D2,0)+1) returns 4. EOMONTH finds month ends, so the formula constructs the first day of D2’s month and the first day of the following month. EOMONTH is available in current desktop Excel; if your organization restricts older compatibility features, check the version in use.

Handle dates that include times

Excel date-times have a whole-number date portion and a fractional time portion. As a result, a COUNTIF equality test against a date at midnight will not match a timestamp later that same day. To count every timestamp falling on one day, use a lower bound at the start of the date and an upper bound at the start of the next date.

Suppose B2:B5 contains March 8, 2025 at 9:00 a.m.; March 8 at 5:30 p.m.; March 9 at midnight; and a blank. With these values in B2:B5, =COUNTIFS(B2:B5,">="&DATE(2025,3,8),B2:B5,"<"&DATE(2025,3,8)+1) returns 2. It includes both March 8 timestamps, but not midnight on March 9. The same method works for a full month of timestamped records by using the first day of the target month and the first day of the next month as boundaries.

If you expected a date count but got zero, hidden times are worth checking. Change the cell’s number format temporarily to show a time, or inspect the value in the formula bar. Formatting a cell to show only a date does not remove its time component; the range test solves the comparison without altering the source data.

Common problems and how to fix them

  • Dates are stored as text. A value may look like a date but remain text, so numeric date comparisons do not work reliably. Re-enter or convert the values into genuine dates, then check whether changing the number format changes their display. Merely applying a date format does not convert text into a date.
  • The date was interpreted in the wrong order. Entries such as 04/05/2025 can mean April 5 or May 4 depending on regional settings. Use DATE(year,month,day) in criteria, and confirm ambiguous imported values before counting.
  • A time is hidden by formatting. Equality criteria match the exact stored value, including any time fraction. Use a lower and upper date boundary when counting a calendar day.
  • One side of a range is accidentally exclusive. > excludes the cutoff date; >= includes it. Likewise, < excludes the upper boundary. Decide explicitly whether the first and last dates should count.
  • The ranges in COUNTIFS do not line up. Each criteria range should cover the same rows and have matching dimensions. Mismatched ranges can cause errors or confusing results.
  • Blanks or errors are mixed into the source. COUNTIF does not count a blank as a date, but error values and formulas returning text can complicate a dataset. Filter or inspect unexpected cells before assuming the formula is wrong.

Also check whether the workbook uses an unusual date system. Most Windows workbooks use Excel’s 1900 date system, while some older Mac workbooks may use the 1904 system. Excel generally handles a workbook’s date system internally, but dates copied between workbooks with different systems can shift. If copied dates appear roughly four years off, inspect the workbook’s date-system setting rather than trying to fix the count formula.

COUNTIF, COUNTIFS, and other ways to count dates

Use COUNTIF when one condition is enough: a particular date, dates before a cutoff, or dates after one. Use COUNTIFS when a count needs two or more conditions, such as a start and end date, or a date range plus a matching department. For multiple conditions across columns, each COUNTIFS criteria range must correspond row by row to the other ranges.

A PivotTable can be more convenient when you need a report grouped by month, year, or another category and want to explore totals without maintaining a separate formula for each period. A filtered table can also help inspect which records contribute to a count. These approaches do not change how Excel stores dates; they offer different ways to summarize the same source records.

COUNTIF is not the right tool for every date question. If you need to count distinct dates rather than rows, first account for duplicates; a simple COUNTIF total counts each matching record. If you need a count by several date categories, use COUNTIFS or a PivotTable. If a formula is part of a larger report, keep the source date column clean and use explicit boundaries so later additions and timestamps are handled predictably.

Frequently Asked Questions

Can COUNTIF count dates in Excel?
Yes. COUNTIF counts cells whose date values meet its criterion. It can match one exact date or apply a comparison such as before or after a date.

How do I count dates in a month?
Use COUNTIFS with a start-of-month criterion and a less-than criterion for the first day of the next month. The exclusive upper boundary includes every day and any timestamp within the month.

Why does COUNTIF return zero for a date I can see?
The cell may contain text instead of a real date, a hidden time, or a date interpreted differently from what you intended. Check the underlying values and use a two-boundary COUNTIFS formula for timestamps.

Can COUNTIF count dates between two cells?
Use COUNTIFS, joining each comparison operator to its corresponding start or end date cell. This lets the limits change when you edit those cells.

Does COUNTIF count duplicate dates?
Yes. It counts matching cells, so if the same date occurs in four rows, a criterion matching that date returns four. It does not automatically count unique dates only.

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.