Arte SheetsLearn. Try. Understand.

Date & Time

Count Records by Age Bracket Directly from Birthdates (EDATE + COUNTIFS)

Count people in age bands such as 25–34 as of a reference date by translating age bounds into EDATE birth-date cutoffs; check the leap-day convention.

Why age brackets reverse into birthdate windows

As of an evaluation date D_ref, a person is at least MinAge years old if their birthdate is on or before EDATE(D_ref, -12 * MinAge), and has not yet turned MaxAge + 1 if their birthdate is strictly after EDATE(D_ref, -12 * (MaxAge + 1)).

Passing those cutoffs into COUNTIFS counts the inclusive age band without a helper column. The example assumes valid birth dates, a reference date on or after each birth, and nonnegative whole-number bounds with MinAge ≤ MaxAge.

Leap-day convention matters: on 28 February 2025 this cutoff method still counts a person born on 29 February 2000 as age 24; they enter age 25 on 1 March. Other rules may use 28 February. Choose the rule required for your purpose before relying on the result.

Age BracketAs-Of DateEarliest Birthdate (> Cutoff)Latest Birthdate (<= Cutoff)
18 to 242026-06-30> 2001-06-30 (not yet 25)<= 2008-06-30 (at least 18)
25 to 342026-06-30> 1991-06-30 (not yet 35)<= 2001-06-30 (at least 25)
35 to 442026-06-30> 1981-06-30 (not yet 45)<= 1991-06-30 (at least 35)

Age-Bracket COUNTIFS Formula

Combine ">" & EDATE($E$1, -12 * (E3 + 1)) with "<=" & EDATE($E$1, -12 * D3) to count the inclusive band using the stated leap-day convention.

=COUNTIFS($B$2:$B$8, ">" & EDATE($E$1, -12 * (E3 + 1)), $B$2:$B$8, "<=" & EDATE($E$1, -12 * D3))

Try the example

Use the original values or change them to test your understanding.

Further reading