Explanation
At the core, this formula uses the COUNTIFS function to count dates that are greater than or equal to the first day of the current month, and less than the first day of the next month. The EOMONTH function is used to create both dates based on the current date, which is supplied by the TODAY function.
=COUNTIFS(dates,">="&EOMONTH(TODAY(),-1)+1,dates,"<"&EOMONTH(TODAY(),0)+1)
To get the first day of the month, we use the EOMONTH function like this:
EOMONTH(TODAY(),-1)+1
EOMONTH returns the last day of the previous month, to which 1 is added to get the first day of the current month. In a similar way, we use EOMONTH to get the first day of the next month:
EOMONTH(TODAY(),0)+1
EOMONTH returns the last day of the current month, to which 1 is added to get the first day of the next month. The literal translation of the logic in COUNTIFS is: greater than or equal to the first day in the current month AND less than the first day of the next month.
Alternatively, you could use less than or equal (<=) operator for the last day like this:
=COUNTIFS(dates,">="&EOMONTH(TODAY(),-1)+1,dates,"<="&EOMONTH(TODAY(),0))
Previous and next months
To count dates in the previous month:
=COUNTIFS(dates,">="&EOMONTH(TODAY(),-2)+1,dates,"<"&EOMONTH(TODAY(),-1)+1)
To count dates in the next month:
=COUNTIFS(dates,">="&EOMONTH(TODAY(),0)+1,dates,"<"&EOMONTH(TODAY(),1)+1)
SUMPRODUCT alternative
You can also count dates in the previous, current, and next month using SUMPRODUCT function together with the TEXT function like this:
=SUMPRODUCT(N(TEXT(EOMONTH(TODAY(),-1),"mmyy")=TEXT(rng,"mmyy")))
=SUMPRODUCT(N(TEXT(TODAY(),"mmyy")=TEXT(rng,"mmyy")))
=SUMPRODUCT(N(TEXT(EOMONTH(TODAY(),1),"mmyy")=TEXT(rng,"mmyy")))
Here, dates are fed into the TEXT function to get a month and year string, which is used for comparison in an array operation inside SUMPRODUCT. The result is an array of TRUE FALSE values, where TRUE represents dates in the month of interest.
The N function is used to change these values to ones and zeros, and SUMPRODUCT simply sums and returns the array.
Related formulas
Count dates by day of week
Get first day of month
Count dates in given year
Related functions
COUNTIFS Function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria, and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells that...
EOMONTH Function
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to move forward in time, and a negative number...
N Function
The Excel N function returns a number when given a value. The N function can be used to convert TRUE and FALSE to 1 and 0 respectively. When given a text value, the N function returns zero.
