Google Sheets: Count Dates in a Month with COUNTIFS

To count dates in a particular month in Google Sheets, use COUNTIFS with a start date and the first day of the following month as boundaries. For dates in A2:A1000 and a month selector in B1, enter =COUNTIFS(A2:A1000,">="&(EOMONTH(B1,-1)+1),A2:A1000,"<"&(EOMONTH(B1,0)+1)). This counts dates in the month and year shown by B1, including entries with times.

How the month-count formula works

COUNTIFS counts cells that meet multiple conditions. The formula above asks for dates that are greater than or equal to the first day of the selected month and less than the first day of the next month. Those two tests define a complete calendar month without needing to know whether it has 28, 29, 30, or 31 days.

Assume B1 contains any valid date in March 2025, such as March 12, 2025. The formula calculates March 1 as the lower boundary and April 1 as the upper boundary. If A2:A1000 contains four March dates, the result is 4. The particular day in B1 does not matter; it is used to identify the month and year.

The upper test is deliberately “less than April 1,” rather than “less than or equal to March 31.” Google Sheets stores dates as numbers and can store a time as a fractional part of a date. A value such as March 31 at 4:30 p.m. is later than midnight at the start of March 31. The exclusive upper boundary includes every time on March 31 while excluding April 1.

Set up a month selector

A selector cell makes the count reusable: change B1 to a date in another month, and the result updates. You can display B1 as a month and year while keeping it as a real date value. Select B1, then choose Format > Number > Custom date and time and apply a display such as “March 2025” if that option is available in the format editor. The underlying date remains available to the formula.

For a fixed month, you can instead put the boundaries directly into the formula. With dates in A2:A1000 and a goal of counting March 2025, enter =COUNTIFS(A2:A1000,">="&DATE(2025,3,1),A2:A1000,"<"&DATE(2025,4,1)). It counts entries from March 1 up to, but not including, April 1. The result is 4 if the range contains four qualifying dates. Change both year and month in the two DATE functions when you need a different fixed month.

For a report where users select a month, the selector-cell version is usually easier to maintain than editing dates inside a formula. Use a genuine date in the selector, not a text label such as “March.” A text label has no year and may not be interpreted as a date consistently.

Count the same month across all years

There is an important distinction between counting a month-year combination and counting a month name regardless of year. The COUNTIFS date-boundary formula counts March 2025 only; it will not include March 2024. If the question is “How many January dates are in this list, whatever the year?”, compare the month number in each date instead.

With valid date values in A2:A1000, this formula counts January dates across all years: =SUMPRODUCT(--(A2:A1000<>""),--(IFERROR(MONTH(A2:A1000),0)=1)). The nonblank test prevents empty cells from being counted, and MONTH returns 1 for January, 2 for February, and so on. If the range contains two January dates in 2024 and three in 2025, the result is 5. Replace 1 with another month number to count a different month.

This all-years calculation is useful for seasonal summaries, but it discards the year distinction by design. If you need January 2025 specifically, use the date-boundary COUNTIFS approach instead. For a summary of all months, a pivot table grouped by date can also make the year and month breakdown visible rather than returning only one total.

Dates with times are counted correctly

Sheets dates are numeric values; times can be represented as fractions of a day. For example, a cell may display “Mar 31” while its underlying value includes a time. Comparing it to the first day of April with a less-than test includes it, which is why the formula uses a half-open interval: the start is included and the next month’s start is excluded.

A common mistake is to use an end condition such as “less than or equal to March 31.” That can miss March 31 entries whose time is after midnight. Another tempting method is to check whether a displayed date contains the text “Mar,” but display formats and locales vary, and text matching does not reliably test the underlying date. Numeric date boundaries are more dependable for real dates.

Check that the cells contain real dates

A cell can look like a date without containing a usable date value. Dates imported from a CSV or copied from another system may be text. A number format can change how a value appears, but formatting text as a date does not necessarily convert that text into a date serial. If the count is unexpectedly zero, inspect a few cells and verify that Sheets recognizes them as dates.

Select a sample date and look at the formula bar. Check whether it behaves like a date when you change its number format.
Try sorting the date column from oldest to newest. Text dates can sort differently from actual dates, especially when their written formats vary.
Check the spreadsheet’s locale under File > Settings. A date written as 03/04/2025 can mean different days under different regional conventions.
Convert imported values to real dates before counting. Depending on the source and format, you may need to use a date-parsing formula or re-import the data with the correct locale; do not assume that changing the cell format alone fixes it.

Mixed columns are particularly troublesome: some values may be numeric dates and others text, blanks, or error values. Clean or standardize the column when possible, then apply the same counting formula to the resulting date values. This also makes later sorting, filtering, and charting more reliable.

Choose the right method for your data

Use date boundaries with COUNTIFS when the report concerns one particular month and year. It is readable, efficient for ordinary date columns, handles timestamps, and works naturally with a selector cell. Use a month-number calculation when you explicitly want the same month across multiple years. Be clear about the year question before choosing: “March” and “March 2025” are not the same reporting period.

One selected month and year: use COUNTIFS with a lower and upper date boundary.
A fixed month and year: use DATE for the two boundaries, or put a date in a selector cell.
A month across all years: compare MONTH values, understanding that the year is ignored.
Text dates or inconsistent imports: standardize the source values before relying on either method.

For a large dataset, avoid calculating a separate month number for every cell if a straightforward bounded COUNTIFS answers the question. A bounded range such as A2:A1000 also makes the intended data area clear. If your list grows, extend the range or use an appropriate open-ended range such as A2:A, keeping the criteria ranges the same size.

Common errors and quick fixes

A zero result does not always mean there are no dates in that month. First check the year in the selector, then confirm the dates are real values and fall inside the specified range. Also check that the formula range points to the date column, not a column containing formatted date text or labels.

If Sheets reports a formula parse error, confirm that the parentheses and quotation marks are balanced and that the criteria are joined to the calculated dates with &. Some spreadsheet locales use semicolons rather than commas between function arguments. In that case, use the separator expected by the file’s locale while keeping the criteria logic intact. Do not replace the comparison operators inside the quoted criteria.

For an expanding list, the criteria ranges in COUNTIFS must correspond to one another. Using A2:A1000 for the first condition and A2:A500 for the second can produce a range-size error. Keep both date criteria on the same range. If you want to count only dates that also meet another condition, add another range-and-criterion pair, such as a status column equal to “Complete.”

Google Sheets and Excel both provide COUNTIFS, DATE, and EOMONTH, so the core logic is similar in Excel. Menu wording and locale behavior can differ, and formulas may use semicolons in some regional settings. The instructions here use Google Sheets in a web browser.

Frequently Asked Questions

Can Google Sheets count dates in a month regardless of year?
Yes. Compare the month number with MONTH, such as the SUMPRODUCT example above for January. That includes January from every year in the range. Use date boundaries instead when the year matters.

Does COUNTIFS include dates with times?
Yes, when you use a start date with “greater than or equal to” and the first day of the next month with “less than.” This includes all times on the final day of the selected month without including the next month.

Why does my formula return zero?
Common causes include selecting the wrong year, using text rather than real date values, or referencing the wrong column. Check a sample cell’s underlying value and confirm that your selector contains a date in the month you intend to count.

Can I count dates in the current month automatically?
You can build the boundaries from TODAY() and EOMONTH, but remember that TODAY() changes as the date advances and Sheets recalculates. For a fixed report, a selector date is often easier to audit; for a live dashboard, dynamic boundaries can be useful.

Should I use COUNTIF or COUNTIFS?
Use COUNTIFS for the two date boundaries because it applies both conditions to the same date range. COUNTIF accepts one condition, so it cannot by itself define both the start and end of a month.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.