How to Calculate Exact Age from a Birthdate using the DATEDIF Function in Excel

When managing human resources data, patient records, or customer databases in Microsoft Excel, you often have a column containing birthdates. While you can easily subtract the birthdate from today’s date to get the number of days someone has been alive, converting that raw number of days into a clean, accurate age in years is surprisingly difficult due to leap years and varying month lengths.

To solve this, Microsoft Excel contains a powerful, “hidden” function called DATEDIF (Date Difference). This function perfectly calculates the exact time between two dates in years, months, or days.

Why is DATEDIF Considered “Hidden”?

If you start typing =DATEDIF into an Excel cell, the standard autocomplete menu will not suggest it, nor will it provide the usual helpful tooltip explaining the required arguments. Microsoft originally created this function in the 1990s to ensure compatibility with ancient Lotus 1-2-3 spreadsheets. They eventually stopped officially documenting it, but left the underlying code in the software. It still works perfectly in every modern version of Excel.

The Syntax of DATEDIF

Because Excel will not help you write it, you must memorize the syntax:

=DATEDIF(start_date, end_date, "unit")

• start_date: The older date (e.g., the person’s birthdate).
• end_date: The more recent date (e.g., today’s date).
• “unit”: A specific text code telling Excel how to format the answer.

How to Calculate Current Age in Years

Assume you have a person’s birthdate in cell A2, and you want their exact age in years as of today.

1. Click on the empty cell where you want the age to appear.
2. Type the following formula: =DATEDIF(A2, TODAY(), "Y")
3. Press Enter.

Let’s break down why this works:

• A2 provides the start date.
• TODAY() is a separate Excel function that constantly checks your computer’s clock. This means the age will automatically update if you open the spreadsheet next year.
• "Y" stands for Years. It tells Excel to count only the fully completed years between the two dates. If a person turns 30 tomorrow, this formula will correctly display 29 today.

Advanced Formatting: Years, Months, and Days

Sometimes “30 years old” is not precise enough. For pediatric data or short-term project management, you may need to know that someone is exactly 30 years and 4 months old. DATEDIF has special unit codes to calculate the remainders.

• “YM”: Calculates the remaining months after subtracting full years.
• “MD”: Calculates the remaining days after subtracting full years and full months.

To create a comprehensive sentence, you can use the ampersand (&) symbol to glue multiple DATEDIF formulas together with text.

To display “30 Years, 4 Months”, use this formula:

=DATEDIF(A2, TODAY(), "Y") & " Years, " & DATEDIF(A2, TODAY(), "YM") & " Months"

This combined formula accurately extracts the full years, adds the text “Years, “, extracts the leftover months, and adds the text “Months”. It provides bulletproof accuracy that simple subtraction formulas cannot match.