How to Calculate Age in Excel: Years, Months and Days

Calculating age in Excel is useful when you need to manage several dates of birth at once. Instead of checking each record manually, you can enter one formula and apply it to an entire list of students, employees, patients, clients, or survey participants.

Excel can calculate completed age in years, display a detailed result in years, months, and days, or update age automatically using today’s date. You can also calculate age on a fixed date, such as a school assessment date, admission date, appointment date, or employment start date.

Quick answer: If the date of birth is in cell A2 and the calculation date is in B2, use =DATEDIF(A2,B2,"Y") to calculate completed age in years.

Why Excel Is Useful for Age Calculation

An online Chronological Age Calculator is convenient when you need one quick result. Excel is more practical when you need to calculate and manage age across many records.

Excel can help you:

• Calculate age for an entire list
• Use the same calculation date for every record
• Update current ages automatically
• Reduce repeated manual work
• Keep age information alongside other data
• Copy the same formula across hundreds of rows

The main requirement is that Excel recognizes both entries as valid dates. The date of birth must also come before the calculation date.

How Excel Calculates Age Between Two Dates

Excel calculates age by comparing two dates:

• The date of birth
• The selected calculation date

The calculation date may be today, a school test date, an admission date, an appointment date, or another official reference date.

For example:

Date of birth: 15 March 2010
Calculation date: 10 June 2026

Excel can use these dates to return completed years or a detailed result containing years, months, and days.

How to Arrange Dates in Your Excel Sheet

Before entering a formula, arrange your worksheet clearly:

Cell or column	Information	Example
A2	Date of birth	15/03/2010
B2	Calculation date	10/06/2026
C2	Calculated age	Formula result

Enter the date of birth in A2 and the date up to which age should be calculated in B2. The age formula will be entered in C2.

Using a separate calculation-date column makes it easier to calculate age on a fixed event date rather than always using today’s date.

The Fastest Excel Formula for Completed Age

The DATEDIF function is one of the simplest ways to calculate completed age in Excel.

If the date of birth is in A2 and the calculation date is in B2, enter this formula in C2:

=DATEDIF(A2,B2,"Y")

The "Y" unit tells Excel to return the number of complete years between the two dates.

Date of birth

Calculation date

Result

15/03/2010

10/06/2026

16

Calculate Age in Years, Months, and Days

To display a complete age breakdown, use:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

The result may appear as:

16 years, 2 months, 26 days

The formula uses:

• "Y" for completed years
• "YM" for remaining months
• "MD" for remaining days

This format can be useful for education, assessments, records, and other situations where completed years alone are not detailed enough.

Calculate Current Age Automatically with TODAY

To calculate a person’s current completed age, use:

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

For a detailed result, use:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"

The result updates automatically as the current date changes.

Use TODAY() only when the age should remain current. Do not use it when you need to preserve age on a past assessment, admission, or appointment date.

Calculate Age on a Fixed Assessment or Record Date

When age must be calculated on a specific date, enter that date in B2 instead of using TODAY().

Use:

=DATEDIF(A2,B2,"Y")

For a detailed result:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

A fixed date is useful for:

• School assessments
• Admission records
• Medical appointments
• Employment records
• Research data
• Historical reports

This prevents the result from changing every day.

Online Chronological Age Calculator vs Excel

Both online tools and Excel formulas are useful, but they are used for different needs.

Method	Best For	Main Benefit
Online chronological age calculator	Quick single age checks	Fast and simple
Excel formula	Large data lists	Handles many records
Manual calculation	Small simple cases	No tool needed
Pearson-style calculation	Assessments	Accurate test-date age

  If you only need one quick result, an online calculator is easier. If you have many records, Excel is better.

		Common Excel Age Formula Errors Age calculation errors usually happen when people count only years and ignore months or days. This can create problems in professional records. Dates are stored as text A date may look correct but still be stored as text. Check the cell format and make sure Excel recognizes it as a date. The formula returns #NUM! This usually happens when the date of birth is later than the calculation date. Make sure A2 contains the earlier date and B2 contains the later date. The formula divides by 365 Avoid: =(B2-A2)/365 This produces an approximate decimal result and may be affected by leap years. Use: =DATEDIF(A2,B2,"Y") when you need completed age in years. Best Practices for Accurate Excel Results For more reliable Excel age calculations: Enter complete dates containing day, month, and year Make sure date cells are formatted correctly Use a fixed calculation date for assessments and records Use TODAY() only for live current-age lists Avoid dividing the number of days by 365 Confirm that the birth date comes before the reference date Test the formula using a result you already know Keep the original date information in your worksheet Check formulas before copying them across a large dataset When age affects an official decision, verify the input dates before relying on the result. FAQs How do I calculate completed age in Excel? Use: =DATEDIF(A2,B2,"Y") A2 should contain the date of birth, and B2 should contain the calculation date. What is Pearson chronological age calculator used for? A Pearson chronological age calculator is used in educational and psychological assessment settings where exact age is needed for scoring, comparison, or interpretation. How do I calculate age in years, months, and days in Excel? Use this formula: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days" How do I calculate age as of today? Use: =DATEDIF(A2,TODAY(),"Y") This result updates automatically. Related Age Calculation Guides Pearson Chronological Age Calculator Manual Chronological Age Calculation Guide Conclusion Excel makes age calculation faster when you need to manage multiple records. The DATEDIF function can calculate completed years, while TODAY() can keep current-age lists updated automatically. The most important choice is the calculation date. Use today’s date when the result should remain current, and use a fixed reference date for assessments, admissions, appointments, employment records, and historical data. Check that Excel recognizes your dates correctly, avoid approximate formulas based on 365 days, and verify the cell references before copying a formula across a large dataset. With a clear worksheet structure and the correct reference date, Excel can provide consistent age results for an entire list.