# How to count dates in a given year in Excel

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.

**Excel:** `=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Hire date |  |  |  | Year | 2025 |
| Lena Park | 2023-11-20 |  |  |  | Hires |  |
| Omar Ruiz | 2024-03-04 |  |  |  |  |  |
| Noor Haddad | 2024-12-31 |  |  |  |  |  |
| Tom Becker | 2025-01-01 |  |  |  |  |  |
| Priya Shah | 2025-02-17 |  |  |  |  |  |
| Ines Lopez | 2025-06-30 |  |  |  |  |  |
| Jon Wells | 2025-09-08 |  |  |  |  |  |
| Mia Chen | 2025-12-31 |  |  |  |  |  |
| Raj Iyer | 2026-01-12 |  |  |  |  |  |
| Sara Nilsen | 2026-03-02 |  |  |  |  |  |

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

Result: 5

## 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.

## Other ways

- `=SUMPRODUCT(--(YEAR(B2:B11)=G1))`: 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))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-dates-in-a-year