To count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as the criterion—for example, =COUNTIF(A2:A8,DATE(2026,1,15)). For a date range, use COUNTIFS with a start date and an exclusive end date; this also works when cells contain times.
How COUNTIF works with dates
COUNTIF counts cells in one range that meet one condition. Its first argument is the cells to inspect, and its second is the condition, called the criteria. Dates can be used as criteria because Excel stores valid dates as numbers: the displayed date is a formatted representation of an underlying serial value. This is why comparing genuine Excel dates works like comparing numbers.
For the examples below, assume A2:A8 contains these seven genuine dates, in order: January 1, January 15, January 15, January 31, February 1, February 15, and February 28, 2026. If those cells are formatted as dates, they may look different from the values Excel uses internally, but the formulas still compare the underlying values.
With the dates above in A2:A8, this formula counts January 15 and returns 2:
=COUNTIF(A2:A8,DATE(2026,1,15))
DATE(year, month, day) constructs a date value without relying on how a date string is interpreted in your regional settings. The date in the formula must match the year, month, and day you want to count.
Count dates before, after, or on a cutoff
For conditions such as before a date or on or after a date, COUNTIF needs a comparison operator in its criteria. Because the operator is part of a text criterion, put it inside quotation marks. When the cutoff is supplied by another cell or built with DATE, join the operator and date with the ampersand (&).
With the sample dates in A2:A8, this counts dates on or after January 15, 2026, and returns 6:
=COUNTIF(A2:A8,">="&DATE(2026,1,15))
The comparison includes both January 15 entries, January 31, and all three February entries. Change >= to > to exclude the cutoff date; change it to < or <= to count dates before, or through, the cutoff. With the same A2:A8 sample, this formula counts dates strictly after January 15 and returns 4:
=COUNTIF(A2:A8,">"&DATE(2026,1,15))
If your cutoff date is in D1, join the operator to that cell instead of typing a date into the formula. With dates in A2:A8 and the cutoff date January 15, 2026, in D1, the formula returns 6:
=COUNTIF(A2:A8,">="&D1)
Make sure D1 is a real Excel date, not text that merely looks like one. Using a cell reference makes it easy to change the cutoff without editing the formula.
Count dates within a range
COUNTIF can apply only one condition, so a range with both a start and an end needs COUNTIFS. COUNTIFS applies all its conditions to the corresponding cells in the ranges you specify. For date intervals, a reliable pattern is “greater than or equal to the first day, and less than the first day after the interval.”
With dates in A2:A8, this formula counts January 2026 and returns 4:
=COUNTIFS(A2:A8,">="&DATE(2026,1,1),A2:A8,"<"&DATE(2026,2,1))
The lower boundary includes January 1, while the upper boundary excludes February 1. This method is useful even if values include times: a time on January 31 is still earlier than February 1 at midnight. It also avoids having to guess the last possible time on the final day of a period.
You can use cell references for flexible boundaries. With dates in A2:A8, a start date in D1, and an end date in E1, this formula counts from D1 through E1, including both dates, and returns 4 when D1 is January 1 and E1 is January 31, 2026:
=COUNTIFS(A2:A8,">="&D1,A2:A8,"<"&(E1+1))
The added 1 advances a genuine date value by one day, turning the inclusive end date into an exclusive boundary. This formula assumes D1 and E1 contain dates without times. For timestamps, the same approach includes every time on the end date.
Count dates in a month or year
To count a calendar month, set the lower boundary to its first day and the upper boundary to the first day of the next month. This is generally more dependable than searching for month names or relying on how dates are displayed. For example, with dates in A2:A8, the January formula above returns 4; changing the boundary dates to February 1 and March 1 counts the February entries and returns 3.
The same pattern works for a year: use January 1 of the year as the inclusive start and January 1 of the next year as the exclusive end. This is helpful when a range contains several years, since it prevents dates from the same month in another year from being included. If the desired year is stored in a cell, you can build its boundaries with DATE by referring to that year and adding 1 for the next year.
For a month that changes, store the first day of the target month in a cell, then use that cell as the lower boundary and the following month’s first day as the upper boundary. The important detail is that both conditions must refer to the same date range. COUNTIFS returns the number of rows where every condition is true; it does not count a row that meets only one of the two boundaries.
What if the cells also contain times?
Excel date-times are numeric values with a fractional part for the time. A value displayed as January 15, 2026 may therefore be January 15 at midnight, while another value displayed the same way might be January 15 at 2:30 p.m. If the time is hidden by the cell format, the entries can look identical even though their underlying values differ.
An exact-date COUNTIF criterion matches the date value at midnight, not every possible time on that calendar day. To count all entries on January 15, use a lower bound at the start of that day and an exclusive bound at the start of January 16. With timestamps in B2:B5—January 15 at 8:00 a.m., January 15 at noon, January 16 at midnight, and January 31 at 6:00 p.m.—the formula returns 2:
=COUNTIFS(B2:B5,">="&DATE(2026,1,15),B2:B5,"<"&DATE(2026,1,16))
This boundary technique also avoids missing late-night entries on the target date. Formatting a cell to show only its date does not remove its time value; changing the display format is not a substitute for setting date-time boundaries in the count.
Dates stored as text and other common problems
COUNTIF may return zero even when dates appear to match if one or more cells contain text rather than Excel date values. Imported data is a common source: a text string such as “2026-01-15” can look like a date but behave differently in comparisons. A quick check is to change the cell format to General. Genuine dates normally display as serial numbers; text stays as text. Formatting alone cannot convert text into a date.
For a column of consistently formatted text dates, Excel’s Text to Columns tool can convert them: select the column, choose Data > Text to Columns, select Delimited and choose Next, choose Next again, select Date and the matching order for the source data (such as YMD), then choose Finish. Check a few converted cells before relying on the count. If the source uses mixed formats or ambiguous dates such as 03/04/2026, confirm whether the source means March 4 or April 3 before converting; the text alone may not make that clear.
Other frequent causes of unexpected results include:
- Using a date string as typed text: A criterion such as a quoted date can be interpreted differently across regional settings. DATE or a reference to a real date cell is safer.
- Including the wrong boundary: Use
>=to include the first date and an exclusive next-day or next-period boundary to avoid omitting times. - Unequal COUNTIFS ranges: The criteria ranges must be the same size and shape; use the same date range for both boundaries.
- Looking at display rather than value: A custom format can hide a time or make different underlying dates appear similar. Inspect the cell contents and format when results are surprising.
Excel for Microsoft 365 on Windows uses commas between formula arguments in the examples here. In some regional configurations, Excel may display or require semicolons instead. If Excel rejects a formula copied from another locale, check the list separator setting rather than changing the date logic. The formulas themselves work in Excel for Mac and Excel for the web as well.
COUNTIF versus COUNTIFS and other approaches
Use COUNTIF for one condition: one exact date, all dates before a cutoff, or all dates after one. Use COUNTIFS when you need a bounded interval or several conditions. For instance, counting January requires two comparisons, so COUNTIFS is the appropriate choice. These functions count qualifying cells; they do not add values from another column or return the matching date records.
If you need to inspect or reuse the matching records rather than only get a total, filtering the date column can be more useful. Excel’s filter controls can show dates in a selected period, and a PivotTable can summarize date records by time periods when the source data is suitable. Those options are alternatives for exploring data, not replacements when a formula-driven count is required.
For a formula that updates as dates are added, use a structured table column or a range large enough for future rows. A fixed range such as A2:A8 will not automatically include a new entry in A9. In a table, structured references can expand as rows are added, making a date count easier to maintain. Whichever approach you use, keep the counted date field consistent: mixed text, dates, and date-times can lead to results that are hard to interpret.
Frequently Asked Questions
Can COUNTIF count one exact date?
Yes. Use a real date criterion, preferably constructed with DATE or referenced from a cell. If the cells include times, use two COUNTIFS boundaries to count the whole calendar day.
How do I count dates in a month in Excel?
Use COUNTIFS with a lower boundary at the first day of the month and an exclusive upper boundary at the first day of the next month. This includes all valid times within the month and avoids matching that month in other years.
Why does COUNTIF return zero for a date that is visibly there?
The cell may contain text instead of a date value, may include a hidden time, or may be a different year than the criterion. Check the underlying value and ensure the criterion and data use compatible date values.
Can COUNTIF count dates with times?
Yes, but an exact-date criterion may match only a value at midnight. To include every time on a day, count values greater than or equal to that day’s start and less than the next day’s start.
Does changing the cell format fix text dates?
No. Formatting changes how a value is displayed, but it does not convert text into a numeric Excel date. Convert the source values first, then apply the date-count formula.
Can I use COUNTIF for dates in Google Sheets?
Google Sheets also has COUNTIF and COUNTIFS, and similar date-boundary logic generally applies. The formulas here are written for Excel; verify that imported date values in either program are recognized as dates rather than text.
Related
- Can You Shuffle Names in Excel? Methods and Examples
- Can You Copy an Excel Spreadsheet? Methods Explained
- Can You Open the Same Excel File Twice?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
- Add a Formula in Excel to Calculate DatesTo calculate a date in Excel, enter a starting date in one cell and use a formula such…
- Excel MATCH Function: Input Types and Match Modes ExplainedIn Excel, MATCH accepts a lookup value, a one-dimensional lookup array, and an optional match type: 0 requests…
- Excel VLOOKUP Multiple Values: Formulas That WorkTo return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With…
- CONCAT in Excel: Join Text and NumbersYes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a…
- Excel Date Difference Formulas for Days, Months, and YearsTo find the number of elapsed days between two dates in Excel, subtract the start date from the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.