How to count dates in a given year in Excel

Q: How do I count dates in the current year?

Use YEAR(TODAY()) in place of G1: =SUMPRODUCT(--(YEAR(B2:B11)=YEAR(TODAY()))).

Q: How do I count by year in a summary table?

Put the years in a column, say E2:E5, and fill =COUNTIFS($B$2:$B$11,">="&DATE(E2,1,1),$B$2:$B$11,"<"&DATE(E2+1,1,1)) down.

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1))

Count from January 1 up to the next January 1: =COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1)), with the year in G1.

Result on the example: 5 in G2
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

How it works

DATE builds the first day of the year from the number in G1, and DATE(G1+1,1,1) the first day of the next year. Counting dates on or after the first and strictly before the second covers the whole year, including December 31.

The hires on January 1 and December 31, 2025 sit on the boundaries and both count, giving 5.

=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1))

Result5

B2:B1110 rows × 1 col: 11/20/2023, 3/4/2024, 12/31/2024, 1/1/2025, …
A range: 10 cells in one column.
G12025
The value in G1.
DATE(G1,1,1)1/1/2025 (45658)
DATE: Builds a date from parts.
- yearG12025
- month1
- day1
">="&DATE(G1,1,1)">=45658"
The & operator joins as text.
G1+12026
The + operator adds.
DATE(G1+1,1,1)1/1/2026 (46023)
DATE: Builds a date from parts.
- yearG1+12026
- month1
- day1
"<"&DATE(G1+1,1,1)"<46023"
The & operator joins as text.
COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1))5
COUNTIFS: Counts rows that meet every condition.
- criteria_range1B2:B1110 rows × 1 col: 11/20/2023, 3/4/2024, 12/31/2024, 1/1/2025, …
- criteria1">="&DATE(G1,1,1)">=45658"
- criteria_range2B2:B1110 rows × 1 col: 11/20/2023, 3/4/2024, 12/31/2024, 1/1/2025, …
- criteria2"<"&DATE(G1+1,1,1)"<46023"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1)). We ran it in Google Sheets mode on the same data and it returns 5.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(--(YEAR(B2:B11)=G1))	5	Reads the year from each date. Simple and works everywhere, but can't use COUNTIFS speed on big sheets.
=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<="&DATE(G1,12,31))	5	Same result for plain dates. Use the "< next January 1" form if the dates include times.

Common mistakes

Typing the year in the criteria, like COUNTIF(B2:B11,2025), looks for the date whose serial number is 2025, a day in 1905, and returns 0.
Text dates are not counted. If some dates are left-aligned, convert them before counting.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count dates in a given year

In G2, count the employees hired in the year in G1.

Questions

How do I count dates in the current year?

Use YEAR(TODAY()) in place of G1: =SUMPRODUCT(--(YEAR(B2:B11)=YEAR(TODAY()))).

How do I count by year in a summary table?

Put the years in a column, say E2:E5, and fill =COUNTIFS($B$2:$B$11,">="&DATE(E2,1,1),$B$2:$B$11,"<"&DATE(E2+1,1,1)) down.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.