Function Problems

How to Calculate Age from Birthdays in an Excel Structured Table

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to calculate individuals' ages based on their birth dates in a structured table, calculating the age relative to a specific target date stored in a column header.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating exact age in years from a list of birthdays relative to a specific date in a header, or current age as of today.
Observed behavior: Age calculations may fail or return errors if the birthday values are stored as text strings (such as dates separated by periods) instead of valid date formats recognized by the software.

Before you start

Ensure all birthday entries are formatted as valid dates. If your dates are stored as text with periods (e.g., 01.01.1990), you will need to clean the data before applying any formulas.

Solution 1Recommended

Calculate age based on a specific date using DATEDIF

Use the DATEDIF function combined with an absolute reference to calculate completed years between a birth date and a specific target date located in a column header.

The DATEDIF function is a specialized formula used to find the difference between two dates. By locking the header date reference, you can easily apply the formula to an entire column.

Select the target cell

Click on the cell in the second table where you want the calculated age to appear.

Enter the DATEDIF formula

Type the formula =DATEDIF(A2, A$1, "Y"). In this formula, A2 represents the cell containing the birth date, and A$1 represents the target date in the column header.

Lock the header row reference

Ensure you use the $ symbol before the row number of the header date (e.g., A$1). This absolute reference prevents the header row from shifting when you copy the formula down.

Apply to the entire column

Press Enter to generate the result, then click and drag the fill handle at the bottom-right corner of the cell down to calculate the age for the rest of the table.

Unit argument explained: The "Y" argument in the DATEDIF function ensures the formula returns the number of full, completed years passed between the two dates.

Solution 2

Convert text dates containing periods to valid dates

If the DATEDIF function returns a #VALUE! error, the birth dates might be formatted as text. Replacing periods with slashes makes the dates recognizable.

Solution 3

Calculate age as of today

Use this method if you need to calculate a person's current age rather than their age on a specific past or future date.

Work seamlessly with spreadsheets

Calculate Ages Easily in WPS Spreadsheet

WPS Spreadsheet provides full support for advanced date and time functions, including DATEDIF and TODAY, making it simple to process and manage data tables. Enjoy a familiar interface that handles all your data calculation needs efficiently.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open the .xlsx file containing your table of birthdays.
2. Format dates correctly: Use Find and Replace (Ctrl + H) to change any periods to slashes to ensure the dates are readable.
3. Apply the DATEDIF formula: In an empty column, type =DATEDIF(A2, TODAY(), "Y") to calculate the current age based on the birthday in A2.
4. Drag to fill the table: Press Enter, click the cell, and double-click the fill handle to automatically calculate ages for the entire structured table.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Includes built-in Find & Replace for quick data cleaning and date formatting.Supports advanced functions like DATEDIF out of the box with zero setup.Lightweight, fast, and completely free to download.

microsoft office alternative - wps office

Frequently Asked Questions

What does the #NUM! error mean when using DATEDIF?

A #NUM! error usually occurs if the start date (birthday) is chronologically greater than the end date (header date or today). Check your formula to ensure the older date is referenced first, followed by the more recent date.

Can I calculate age in months instead of years?

Yes, you can change the third argument in the DATEDIF function from "Y" to "M". This will calculate the total number of completed months between the birth date and the target date.

Why does my formula return a #VALUE! error?

A #VALUE! error typically indicates that one or both of the dates in the formula are formatted as text rather than valid dates. Ensure you convert any text dates (like those using periods) to actual dates using Find and Replace.

How can I display the age with the word "years" attached to it?

You can use the ampersand operator (&) to concatenate text to your formula result. For example, typing =DATEDIF(A2, TODAY(), "Y") & " years" will output a result like "30 years".

Excel & Data Problems