Cellwise Start free

Excel guide · 5 min read

How to count dates by month in Excel

Quick answerPut the first day of each month in a column (F3 = 1 Dec 2026), then use =COUNTIFS($A$2:$A$12,">="&F3,$A$2:$A$12,"<"&EDATE(F3,1)) and fill down. It counts every date from the 1st of that month up to, but not including, the 1st of the next, so times on the last day and different years are handled correctly.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHI
1OpenedChannelStatusMonthTicketsFeb export (text)Month name
2Nov 28, 2026EmailClosedNov 1, 20262027-02-03January
3Dec 2, 2026PhoneClosedDec 1, 20262027-02-08
4Dec 9, 2026EmailClosedJan 1, 20272027-02-11
5Dec 17, 2026ChatOpen2027-02-20
6Dec 31, 2026 16:40EmailOpenYear2027
7Jan 5, 2027PhoneClosedMonth no.1
8Jan 12, 2027EmailOpen
9Jan 19, 2027ChatClosed
10Jan 26, 2027EmailOpen
11PhoneOpen
12Jan 14, 2026EmailClosed

Download this sheet as an Excel practice file (.xlsx)

A monthly count table with COUNTIFS

Column A holds the date each support ticket was opened. To count tickets per month, list the first day of each month in F2:F4 and put the formula in G2, then fill it down. Format F as Custom mmm yyyy so it reads "Dec 2026" while staying a real date the formula can use.

EDATE(F3,1) is the 1st of the following month, so the two conditions together mean "on or after 1 Dec and before 1 Jan". The $ signs keep the data range fixed while F3 moves down to F4. To count only some tickets, add a pair: =COUNTIFS(A2:A12,">="&F3,A2:A12,"<"&EDATE(F3,1),C2:C12,"Open") returns 2 open tickets from December.

=COUNTIFS($A$2:$A$12,">="&F3,$A$2:$A$12,"<"&EDATE(F3,1))

Result: 4

4 tickets opened in December 2026, including the one at 16:40 on the 31st. Filled down, November gives 1 and January 2027 gives 4.

Why "<" the next 1st beats "<=" the last day

A common version ends the range at the last day of the month with EOMONTH. That works only when the dates have no time. The ticket in A6 was opened at 16:40 on 31 Dec, which Excel stores as 31 Dec plus 0.69 of a day. "<=" 31 Dec means midnight at the start of the 31st, so that ticket falls outside the range.

Dates exported from help desks, web forms and card terminals usually carry a time even when the cell format hides it. Ending with "<" the next month's 1st catches every moment of the last day, so it is the safer habit.

=COUNTIFS($A$2:$A$12,">="&F3,$A$2:$A$12,"<="&EOMONTH(F3,0))

Result: 3

3, one short: the ticket opened at 16:40 on 31 Dec is missed. The "<" next-month version above gives 4.

Count one month picked by year and month number

For a dashboard where someone types a year and a month number, build the boundaries with DATE. G6 holds 2027 and G7 holds 1, so DATE(G6,G7,1) is 1 Jan 2027 and DATE(G6,G7+1,1) is 1 Feb 2027.

You do not need a special case for December. DATE rolls a month of 13 into January of the next year, so =COUNTIFS(A2:A12,">="&DATE(2026,12,1),A2:A12,"<"&DATE(2026,13,1)) counts December 2026 and returns 4. Add more pairs as before; adding B2:B12,"Email" gives 2 email tickets in January.

=COUNTIFS(A2:A12,">="&DATE(G6,G7,1),A2:A12,"<"&DATE(G6,G7+1,1))

Result: 4

4 tickets in January 2027. The January 2026 ticket in A12 and the undated row are not counted.

The MONTH() shortcut and its two traps

Counting with MONTH looks simpler, and it is the formula many people reach for. But MONTH(A2:A12)=1 matches January of every year, so last year's ticket in A12 is counted. It also matches the empty cell in A11: an empty cell is treated as 0, and day 0 in Excel falls in January 1900, so MONTH returns 1.

If you use MONTH, always pair it with YEAR: =SUMPRODUCT((MONTH(A2:A12)=1)*(YEAR(A2:A12)=2027)) returns the correct 4, and the year test also removes the blank row. MONTH alone is only safe when you really want "every January" across years and the range has no blanks.

=SUMPRODUCT(--(MONTH(A2:A12)=1))

Result: 6

6, not 4: it includes the January 2026 ticket and the row with no date.

Text dates count as 0

Column H holds four February tickets pasted from a CSV. They look like dates, but they are text. A criterion like ">="&DATE(2027,2,1) is a number test, and COUNTIFS never counts a text cell as passing it, so none of them falls between the two dates. There is no error, just a 0 that looks believable.

Check with =COUNT(H2:H5), which counts only real numbers and dates: it returns 0 here, while =COUNTA(H2:H5) returns 4. Convert the column for good with the steps below. If you cannot change the data, DATEVALUE can convert inside the formula: =SUMPRODUCT((DATEVALUE(H2:H5)>=DATE(2027,2,1))*(DATEVALUE(H2:H5)<DATE(2027,3,1))) returns 4. DATEVALUE reads text using your system's date settings, so check one result by eye.

  1. Select H2:H5.
  2. Go to Data > Text to Columns, choose Delimited and click Next twice.
  3. Under Column data format choose Date and pick the order the text uses (YMD here), then click Finish.
=COUNTIFS(H2:H5,">="&DATE(2027,2,1),H2:H5,"<"&DATE(2027,3,1))

Result: 0

0, even though all four February tickets are in the range, because they are text rather than dates.

No formulas: a PivotTable count by month

For a one-off report, a PivotTable counts every month at once. In Excel 365, putting a date field in Rows often groups it into Years, Quarters and Months automatically; if not, group it yourself as below. Include Years in the grouping, or January 2026 and January 2027 are merged into one "Jan" row.

If Excel says it cannot group that selection, something in the date column is not a real date, most often a date stored as text. Fix those cells first, then refresh the PivotTable with right-click > Refresh.

  1. Click any cell in the data and go to Insert > PivotTable, then OK.
  2. Drag Opened to Rows and Channel to Values. A text field in Values shows Count of Channel.
  3. If the dates are not grouped, right-click any date in the PivotTable, choose Group, select Months and Years, and click OK.

Common mistakes

Ending the range with "<=" the last day of the month

Dates with a time on the last day are missed. Use "<" the 1st of the next month, from EDATE(start,1) or EOMONTH(start,0)+1.

Counting with MONTH() only

It mixes years together and counts blank cells as January. Add a YEAR condition, or use COUNTIFS with a start and end date.

Typing the month into the criteria as text, like ">=Dec 2026"

Point the criteria at a cell that holds a real date, or build it with DATE(2026,12,1), and join it with &.

Dates stored as text

COUNTIFS returns 0 for them without an error. Compare COUNT with COUNTA, then convert with Data > Text to Columns.

FAQ

How do I count the number of dates in a month in Excel?

Use COUNTIFS with two conditions on the same column: on or after the 1st of the month, and before the 1st of the next month. For December 2026: =COUNTIFS(A2:A12,">="&DATE(2026,12,1),A2:A12,"<"&DATE(2027,1,1)).

Can I count by month name, like "January"?

Yes. TEXT turns each date into its month name, so =SUMPRODUCT((TEXT(A2:A12,"mmmm")=I2)*(YEAR(A2:A12)=2027)) counts the January 2027 dates when I2 holds "January". The month names follow Excel's language, so in a non-English Excel the names and format codes differ.

How do I count dates in the current month?

Use TODAY() to build the boundaries: =COUNTIFS(A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<"&EOMONTH(TODAY(),0)+1). The result changes as the month changes, so for a fixed report put the month's 1st in a cell instead.

Why does my count by month return 0?

Usually the dates are text, or the criteria were typed as text instead of joined to a date with &. Check with =COUNT(range): if it is lower than the number of dates you can see, some of them are text.

How do I count by month and by another column?

Add more range and criteria pairs to COUNTIFS. =COUNTIFS(A2:A12,">="&F3,A2:A12,"<"&EDATE(F3,1),C2:C12,"Open") counts open tickets in the month that starts in F3.

Functions used

Related guides

Updated