Date & Time
Count Records by Age Bracket Directly from Birthdates (EDATE + COUNTIFS)
Count how many people fall into age bands like 25–34 or 35–44 as of a valuation date without adding a helper age column by translating age bounds into exact EDATE birth-date cutoffs.
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 two EDATE cutoffs directly into COUNTIFS counts exact completed-year age cohorts across HR census tables without requiring a DATEDIF helper column.
| Age Bracket | As-Of Date | Earliest Birthdate (> Cutoff) | Latest Birthdate (<= Cutoff) |
|---|---|---|---|
| 18 to 24 | 2026-06-30 | > 2001-06-30 (not yet 25) | <= 2008-06-30 (at least 18) |
| 25 to 34 | 2026-06-30 | > 1991-06-30 (not yet 35) | <= 2001-06-30 (at least 25) |
| 35 to 44 | 2026-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 everyone whose completed age lies inclusively between Min_Age (D3) and Max_Age (E3).
=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.