Function Problems

How to Calculate Age and Assign Groups in Excel

John Wilson Sep 28, 2026 872 views

Question details

The user needs to calculate exact ages based on dates of birth and a reference date, and then automatically assign individuals into specific age brackets (e.g., 6-8, 8-10.5, 10.5-14) that support half-term updates.

How to Calculate Age and Assign Groups with Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Categorizing members, students, or participants into age-specific groups or classes based on precise age requirements (including half-years) for reporting or enrollment.
Observed behavior: Requires a reliable formula to evaluate birthdates against a specific date, calculate the elapsed time in months or years, and output the correct group name without manual sorting.

Before you start

Ensure all dates of birth are formatted as valid dates in your spreadsheet. Decide whether you will use the current date (TODAY) or a fixed half-term cutoff date as your reference point for age calculation.

Solution 1Recommended

Use LET, DATEDIF, and IFS to Assign Groups by Months

This is the recommended approach for high precision. It calculates the exact age in months, which perfectly handles half-year brackets (like 10.5 years) and dynamically assigns the corresponding group.

By calculating age in months, you avoid rounding errors associated with partial years. For example, 10.5 years is exactly 126 months.

Select the target cell

Click on the cell where you want the age group to appear (e.g., E2, assuming the date of birth is in cell D2).

Enter the formula

Type the following formula: =LET(m,DATEDIF(D2,TODAY(),"m"),IFS(m<60,"Under 6 years",m<96,"Group 1",m<126,"Group 2",m<168,"Group 3",TRUE,"14 years and over"))

Apply to the rest of the column

Press Enter to see the result, then click and drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to all other members in the list.

Using a Custom Reference Date: If you need to calculate age based on a specific half-term date rather than today's date, replace TODAY() in the formula with an absolute reference to a cell containing your cutoff date, such as $H$1.

Solution 2

Use DATEDIF and VLOOKUP with a Reference Table

Ideal for scenarios where age brackets might change frequently, as you only need to update a separate lookup table instead of modifying the formula.

Advanced Spreadsheet Capabilities

Calculate and Categorize Data Seamlessly with WPS Office

WPS Spreadsheet fully supports advanced functions like DATEDIF, IFS, and VLOOKUP. You can seamlessly calculate ages and assign groups with high precision, enjoying a smooth and familiar data management experience.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your member list and dates of birth.
2. Apply the Formula: Select the target cell next to the birthdate and paste your DATEDIF or IFS formula directly into the formula bar.
3. Drag to Fill: Use the intuitive fill handle to drag the formula down, instantly categorizing all members into their correct age groups.

Fully compatible with Microsoft Excel formula syntax and formattingBuilt-in function helper to easily troubleshoot complex nested formulasLightweight and fast performance, even when processing large lists of membersFree to use for everyday spreadsheet categorization tasks

Frequently Asked Questions

Why does my DATEDIF formula return a #NUM! error?

This error occurs if the start date (date of birth) is greater, or more recent, than the end date (reference date or TODAY). Ensure your reference date is chronologically after the birth dates.

Can I group ages by years instead of months in the IFS formula?

Yes. Change the "m" in the DATEDIF function to "y" to calculate the age in complete years. You will then need to update the thresholds in your IFS function to reflect years (e.g., m<8 instead of m<96).

How do I easily update the age groups next term?

If you are using the VLOOKUP method, simply change the values in your reference table. If you are using the LET/IFS method, you can either update the thresholds directly in the formula or link the reference date to a specific cell that you can update each term.

What does TRUE mean at the end of the IFS function?

In an IFS function, TRUE acts as a catch-all condition. If none of the previous age conditions are met, the formula defaults to the value assigned to TRUE, which in this case categorizes the remaining members as '14 years and over'.

Excel & Data Problems