To calculate age from a date of birth in Excel, use either the YEARFRAC or DATEDIF function. 1. For precise calculation, use =INT(YEARFRAC(birthdate_cell, TODAY())) which accounts for leap years. 2. Alternatively, use =DATEDIF(birthdate_cell, TODAY(), "Y") for simplicity and readability. 3. Ensure dates are correctly formatted as Date and not text, using DATEVALUE() or Text to Columns if necessary. Proper formatting is crucial for accurate results regardless of the method chosen.

Calculating age from a date of birth in Excel is something a lot of people need to do — whether it's for HR spreadsheets, customer data, or personal use. The good news is, it’s not hard once you know the right formula.

Use the TODAY and YEARFRAC functions together

One of the most accurate ways to calculate age is by combining TODAY() and YEARFRAC().
The formula looks like this:

=INT(YEARFRAC(birthdate_cell, TODAY()))

For example, if the date of birth is in cell A2, the formula becomes:

=INT(YEARFRAC(A2, TODAY()))

• YEARFRAC calculates the number of years between two dates, including fractions.
• INT removes the decimal part, giving you the person’s current age in whole years.

This method accounts for leap years and gives a precise result based on today’s date.

Try DATEDIF for simplicity

If you're using an older version of Excel (or just prefer a more straightforward function), DATEDIF works well too. It might not show up in the autocomplete suggestions, but it’s fully functional.

Use it like this:

=DATEDIF(birthdate_cell, TODAY(), "Y")

Again, assuming the DOB is in A2:

=DATEDIF(A2, TODAY(), "Y")

• This directly returns the number of full years between the DOB and today.
• It’s simple and easy to read, which makes it great for shared spreadsheets.

Format your dates correctly

This step is often overlooked, but it's crucial. If Excel doesn’t recognize the date format, your formulas won't work.

Make sure:

• The DOB column is formatted as Date, not text.
• You can check this by selecting the cell and looking at the Home tab → Number group.
• If needed, convert text to date using DATEVALUE() or the "Text to Columns" feature.

Also, avoid manually typing dates without checking how Excel interprets them — regional settings can cause confusion between day/month/year formats.

That’s basically all there is to it. Whether you go with YEARFRAC or DATEDIF, both methods are solid and widely used. Just make sure your date formatting is consistent, and you’ll get accurate results every time.

The above is the detailed content of how to calculate age from date of birth in excel. For more information, please follow other related articles on the PHP Chinese website!