Excel COUNTIF Dates Within a Month: Formula and Tips

Formulas & functions · Updated November 19, 2026

To count dates in a particular month in Excel, use COUNTIFS with a start date and an exclusive end date. If your dates are in A2:A100 and D1 contains any real date in the month you want, enter =COUNTIFS(A2:A100,">="&EOMONTH(D1,-1)+1,A2:A100,"<"&EOMONTH(D1,0)+1). This counts every date from the first day of D1’s month up to, but not including, the first day of the following month.

Why COUNTIFS Is Better Than COUNTIF Here

COUNTIF checks a range against one condition. A month, however, has two boundaries: the date must be on or after its first day and before the next month begins. COUNTIFS supports multiple conditions on the same range, so it can apply both boundaries at once. The function name in the question is a common shorthand; for a dependable month count, COUNTIFS is usually the right choice.

The formula uses D1 as a month selector. D1 can contain any date in the desired month; it does not have to be the first or last day. For example, if D1 contains 15-Jan-2025, the formula counts dates from 1-Jan-2025 through 31-Jan-2025. It also distinguishes January 2025 from January in other years, which is important when a worksheet contains several years of records.

How Excel Stores Dates

Excel stores valid dates as serial numbers, with the time of day represented as a fractional part of the number. Formatting changes how a value looks, but not the underlying value. A cell displayed as 15-Jan-2025 might therefore contain a date serial, while a cell that looks identical could contain text pasted from another system. COUNTIFS can compare actual date values reliably; text that merely resembles a date may not behave as expected.

The formula’s lower boundary is the first day of the selected month. EOMONTH(D1,-1) finds the final day of the previous month, and adding one gives the first day of D1’s month. EOMONTH(D1,0) finds the final day of D1’s month; adding one gives the start of the next month. Using a less-than comparison for that next-month boundary avoids problems with times on the last day.

Set Up and Use the Formula

For the basic setup, put dates in A2:A100 and enter a date from the month you want to count in D1. The formula returns a single count. D1 can be formatted as a readable month and year without changing its value; for example, a date such as 15-Jan-2025 can display as January 2025.

  1. Check that the entries in A2:A100 are Excel dates, not date-like text.
  2. Enter any date in the target month in D1.
  3. Place the formula in another cell, such as E1, and press Enter.
  4. Change D1 to a date in another month to update the count.

For a worked example, suppose A2:A9 contain 1-Jan-2025, 15-Jan-2025, 31-Jan-2025, 1-Feb-2025, 15-Jan-2025 at 2:00 p.m., 31-Dec-2024, 31-Jan-2026, and a blank. If D1 contains 15-Jan-2025, the formula returns 4. The four qualifying entries are the three January 2025 dates plus the January 15 entry with a time. The February date, December date, 2026 date, and blank do not meet both conditions.

Why the End Boundary Excludes the Next Month

A tempting approach is to count dates less than or equal to the final calendar day of the month. That can fail when the source includes timestamps. A date-time on January 31 at 2:00 p.m. is later than January 31 at midnight, even though it is still in January. A condition of less than or equal to the date serial for January 31 can omit it.

The formula instead counts values that are strictly less than February 1. Every time on January 31 is earlier than that boundary, while February 1 at midnight is not. The same logic works for months of different lengths and for leap-year February, without having to enter a month’s final day manually.

Counting a Month in a Specific Year

A month name alone is not a unique date range. If a list spans multiple years, counting every date whose month is January would mix January 2024, January 2025, and any other January in the data. The date-range approach avoids this by using both the month and the year from D1 to calculate the boundaries. Choose a date in the exact month and year needed.

This is also why a display format such as mmmm is not a good substitute for a date boundary. It only changes the appearance of a value, and it can hide the year from view. If users choose months from a list, make sure the selected value remains a real date; a label like January 2025 stored as text is not equivalent to a date serial.

Common Problems and Fixes

  • The result is zero. Check that D1 is a valid date and that source dates are numeric Excel date values. Text imports, leading apostrophes, and inconsistent regional date formats can prevent comparisons from working. Try selecting a source cell and changing its number format to a date; if it does not behave like a date value, clean or convert the source data.
  • The count is too high. Confirm that the formula points to the intended date column and that D1 has the correct year. The formula counts all valid values inside the month, including duplicate dates; it counts rows, not distinct calendar days.
  • Dates with times are missing. Use the next-month, less-than boundary shown above. Do not replace it with a less-than-or-equal test against the last day’s midnight.
  • The formula shows an error after copying. Keep the source range and month selector references correct. If you copy the formula to other cells, use absolute references such as $A$2:$A$100 and $D$1 when you need those references to stay fixed.
  • Excel rejects the commas. The formula here uses English-language Excel syntax with comma separators. Depending on regional settings, Excel may expect semicolons between arguments; use the separator configured for your installation.

When a Different Approach Makes Sense

For a straightforward count of records in one month, COUNTIFS is concise and efficient. If you already have separate start-date and end-date cells, the same principle applies: set the first condition to greater than or equal to the start and the second to less than the first day after the range. For month-based reporting, using a real date in a selector cell is generally easier to maintain than typing boundary dates into the formula each time.

Another option is a PivotTable grouped by date, which can be useful when you want counts for many months at once rather than one selected month. A PivotTable can summarize dates by month and year when Excel recognizes the source values as dates. It is more setup than one formula, but it gives a reusable summary view. If the source data changes, refresh the PivotTable to update its results.

Frequently Asked Questions

Can I use COUNTIF to count dates in a month?
COUNTIF alone applies one criterion, so it cannot directly enforce both the first-day and next-month boundaries. Use COUNTIFS for the two conditions. It is more reliable than trying to turn dates into month-name text.

Does the formula include the first and last day of the month?
Yes. The lower condition includes the first day. The upper condition excludes the first day of the next month, which includes all times on the final day of the target month.

Will it count dates that include a time?
Yes, provided the cells contain real Excel date-time values. A timestamp during the selected month falls within the same start and next-month boundaries as a date without a time.

How do I count dates for a month across multiple years?
The formula using D1 counts only the month and year represented by D1. To count, for example, every January regardless of year would require different criteria; that is a different question from counting one calendar month in one year.

Why does a date that looks correct not count?
It may be text rather than a numeric Excel date, or it may belong to a different year than the selected month. Check the underlying value, convert imported text dates to real dates if needed, and verify D1’s year as well as its month.

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.