How to Calculate Age from Date of Birth in Excel: A Simple Guide

Essential Formulas for Converting Date of Birth to Age

To address different reporting needs—whether you require a clean whole number, a precise decimal value, or a detailed breakdown of years, months, and days—you can utilize the following formulas to convert a date of birth to age in Excel. Each method leverages specific time-calculation functions to ensure mathematical accuracy.

Formula 1: Calculating Age in Whole Years (e.g., returning “23”)

This formula employs the DATEDIF function combined with the NOW function to determine the complete number of years passed up to the moment the spreadsheet is opened. The “y” unit ensures that the result is rounded down to the most recent birthday.

=DATEDIF(A2,NOW(),"y")

Formula 2: Calculating Age in Decimal Years (e.g., returning “23.567”)

For high-precision calculations often required in scientific or statistical analysis, the YEARFRAC function is utilized. This formula calculates the fraction of a year represented by the number of whole days between the start and end dates. This provides a decimal age that reflects the exact progression through the current year.

=YEARFRAC(A2,NOW())

Formula 3: Calculating Age in Years, Months, and Days (e.g., returning “23 years, 6 months, 25 days”)

Achieving the highest level of precision requires combining multiple instances of the DATEDIF function, each using a different unit code. The ampersand (&) operator is used to concatenate these numerical results with descriptive text labels, producing a human-readable string that shows the complete age breakdown.

=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"

It is essential to note that for all the formulas presented above, we assume that the originating date of birth data is consistently housed in cell A2 of your spreadsheet. You would adjust this cell reference (A2) according to the location of the first date in your data list.

Understanding the DATEDIF Function: Syntax and Arguments

Before diving into the practical examples, a deeper understanding of the structure of the DATEDIF function is beneficial. Unlike most other Excel functions, DATEDIF is technically a “hidden” function, meaning it does not appear in the Function Wizard, but it is fully operational. Its syntax is straightforward: DATEDIF(Start_Date, End_Date, Unit).

The Start_Date argument should always be the earlier date, which in age calculations is the date of birth. The End_Date is the later date, usually the current date, best referenced using the NOW function to ensure the age dynamically updates every time the worksheet recalculates. The use of NOW() makes the resulting age calculation current and precise.

The Unit argument is a text code enclosed in quotation marks that dictates the type of interval calculation performed. For age conversion, the following codes are most relevant:

• “Y”: Calculates the number of whole years between the dates.
• “M”: Calculates the number of whole months between the dates.
• “D”: Calculates the number of whole days between the dates.
• “YM”: Calculates the number of whole months remaining after subtracting whole years. This is crucial for precise age display (e.g., 2 years and X months).
• “MD”: Calculates the number of whole days remaining after subtracting whole years and whole months. This provides the final daily precision.

Practical Application: Setting Up the Data Table

To demonstrate the functionality of these calculation methods, we will apply them to a simple data set consisting of a list of birth dates. This visual guide will help clarify how the formulas interact with your actual spreadsheet structure.

For our examples, imagine you have a column (Column A) populated with various dates of birth. We will use Column B to input the respective formulas and display the calculated age.

The following figure illustrates the sample data setup in Excel, with the dates of birth starting in cell A2:

This foundational data set is what we will use across the subsequent three examples, showing how different formulas yield vastly different, yet equally accurate, representations of age.

Example 1: Calculating Age Expressed in Whole Years

The most common requirement is simply the age in completed years, ignoring the months and days passed since the last birthday. This method is the simplest implementation of DATEDIF.

To convert the date of birth located in cell A2 into an age expressed solely in whole years, we enter the following concise formula into cell B2:

=DATEDIF(A2,NOW(),"y")

Once the initial formula is entered, you can efficiently calculate the age for the entire list by using the autofill handle. Click and drag this formula down to each remaining cell in Column B corresponding to your list of dates in Column A.

This action applies the relative cell referencing, ensuring that A3 references the date in A3, A4 references the date in A4, and so on, while the NOW function consistently provides the current date as the end point for all calculations.

As clearly displayed in the resulting Column B, the output shows the exact age for each corresponding birth date in Column A, expressed purely in terms of complete years. This result is typically formatted as a general number.

Example 2: Calculating Age in Decimal Years for High Precision

When detailed analysis or modeling requires an age figure that reflects the exact proportion of the current year completed, the decimal age calculation is necessary. This approach moves beyond simple integer representation and provides continuous data.

Instead of DATEDIF, we rely on the YEARFRAC function. This function calculates the fraction of the year represented by the number of days between two dates (the start date and the end date). To calculate the decimal age, enter the following formula into cell B2:

=YEARFRAC(A2,NOW())

It is important to understand that the YEARFRAC function calculates the fraction of the year that has passed since the start date relative to the current date. When the start date (A2) is the date of birth, and the end date is NOW(), the result is the age expressed as a decimal number.

As before, apply the autofill feature by clicking and dragging this formula down through the remaining cells in Column B. This action instantly populates the age in decimal format for every date of birth in your data set.

The resultant Column B displays the age for each individual in a precise decimal format. Notice how the values now include fractional components, indicating how far into the current year the person is.

Example 3: Calculating Age with Years, Months, and Days Precision

For reporting purposes where the full age breakdown is required (e.g., “35 years, 8 months, and 12 days”), a complex concatenation of the DATEDIF function is necessary. This approach uses three separate DATEDIF calls to extract the years, the remaining months, and the remaining days, respectively.

To convert the date of birth in cell A2 into a fully articulated age string, input the following comprehensive formula into cell B2. This formula leverages the “y”, “ym”, and “md” unit arguments to calculate and combine the necessary components:

=DATEDIF(A2,NOW(),"y")&" years, "&DATEDIF(A2,NOW(),"ym")&" months, "&DATEDIF(A2,NOW(),"md")&" days"

It is crucial to understand the role of the specialized units “ym” and “md”. The “ym” unit returns the number of whole months remaining after the years have been calculated, preventing the double-counting of time. Similarly, the “md” unit returns the number of whole days remaining after both years and months have been accounted for, ensuring the final date calculation is accurate down to the day.

Subsequently, click and drag this formula down to apply the precise age calculation to each subsequent date of birth in Column A. Since this formula returns a text string (due to the concatenation of numbers and words), the cell formatting should be set to General or Text.

Column B now clearly displays the age for each date of birth in Column A in the highly detailed format of years, months, and days, providing a complete time elapsed measurement.