How to calculate age in years and months and days from date of birth in Excel?
In many practical situations, such as human resources management, healthcare records, student databases, or membership systems, it's important to accurately calculate an individual's exact age in years, months, and days based on their date of birth. If you have a list of birth dates in Excel, you might need to quickly determine each person's precise age, either in a detailed format showing years, months, and days, or as a decimal or integer value. Achieving this helps to improve data accuracy for tasks such as eligibility checks, policy applications, and milestone tracking. In this article, you will find several methods to perform these age calculations efficiently in Excel.
Table of Contents
Calculate age in years and months and days from date of birth
Calculate age in decimal number from date of birth
Calculate age in integer number from date of birth 
VBA Code - Automate age calculation across a large dataset with custom macros
Other Built-in Excel Methods - Use Power Query to calculate age by transforming data
Calculate age in years and months and days from date of birth
One practical way to calculate age in a detailed format—and one that's widely used for reporting and eligibility decisions—is through a formula that returns years, months, and days between a date of birth and a specific reference date (often today's date). This way is highly suitable for scenarios where the exact span of time is critical, such as for anniversaries, enrollment requirements, or benefits calculations.
Select the cell where you want the age result to appear, then enter this formula:
=DATEDIF(A2,$F$2,"Y")&" Years, "&DATEDIF(A2,$F$2,"YM")&" Months, "&DATEDIF(A2,$F$2,"MD")&" Days"
After entering the formula, press Enter. You can then use Excel's AutoFill handle to drag the formula down to fill additional rows as needed.
In this formula, A2 refers to the cell containing the birth date, and F2 should contain the reference date (such as today's date). This structure provides an exact breakdown, but take care that both cells are formatted as dates. If you encounter a #NUM! error, it may mean the birth date is after the reference date, so double-check your inputs.
Calculate age in decimal number from date of birth
For cases where you need to present age as a decimal—such as for statistical reports, quick age grouping, or eligibility cutoffs—a compact formula is suitable. This approach gives you an age figure like "24.11", making it easy to analyze or filter data.
Select the cell where you want the decimal age to be shown, then enter the following formula:
=DATEDIF(A2,$F$2,"Y")&"."&DATEDIF(A2,$F$2,"YM")
Press Enter to confirm the formula, and drag the fill handle down to automatically apply it to other rows.
As with the previous method, A2 is your date of birth and F2 should be set as the current or reference date. Make sure that date cells are correctly formatted. While this method is straightforward and makes it easy to work with numerical data, be aware it doesn't account for days—if that level of detail is needed, use the full breakdown method instead.
Calculate age in integer number from date of birth
If your goal is to obtain a whole number for age—often used for educational eligibilities, insurance, or access control—the integer age result is sufficient and commonly required. Kutools for Excel streamlines this process by offering a dedicated "Calculate age based on birthday" function, saving you from entering complex formulas manually. This is especially helpful when working with extensive lists or repeatedly performing this calculation.
After free installing Kutools for Excel, follow these steps:
1. Select the cell where you want the calculated age, then click Kutools > Formula Helper > Formula Helper.
2. In the Formula Helper dialog box, choose Calculate age based on birthday from the Choose a formula section, then select the cell containing the birth date for the Date textbox, then click OK.
3. You will now have the age calculated as an integer; drag the fill handle down as needed to apply to other cells. This approach avoids manual entry errors and makes recurring age calculation tasks simpler.
Tip: If you wish to have the age update automatically as time passes, ensure the reference date is set to =TODAY() in Excel.
VBA Code - Automate age calculation across a large dataset with custom macros
When you need to process thousands of records and require age in years, months, and days for each, manually dragging formulas may become time-consuming. A VBA macro allows you to automate age calculation for an entire column at once, saving a lot of repetitive work and ensuring consistency—even if your list of birth dates changes frequently. This method is best for advanced users comfortable with code or those managing fleet data updates.
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. Then, click Insert > Module and enter the following code into the module:
Function AgeYMD(ByVal dob As Date, ByVal refDate As Date) As String
'Updated by Extendoffice 20250902
Dim y As Long, m As Long, d As Long
Dim t As Date
If Not IsDate(dob) Or Not IsDate(refDate) Or refDate < dob Then
AgeYMD = "Invalid date"
Exit Function
End If
' Years
y = Year(refDate) - Year(dob)
If DateSerial(Year(refDate), Month(dob), Day(dob)) > refDate Then y = y - 1
' Move dob forward by y years
t = DateAdd("yyyy", y, dob)
' Months
m = DateDiff("m", t, refDate)
If DateAdd("m", m, t) > refDate Then m = m - 1
' Move temp forward by m months
t = DateAdd("m", m, t)
' Days (now always >= 0)
d = refDate - t
AgeYMD = y & " Years, " & m & " Months, " & d & " Days"
End Function
Sub CalculateAgesAllRows()
Dim ws As Worksheet, lastRow As Long, birthCell As Range, resultCell As Range
Dim refDate As Date, xTitleId As String
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Example prompt; default is today. Make sure you enter a valid date (e.g., 2025-09-02)
refDate = Application.InputBox("Enter reference date (e.g., 2025-09-02)", _
xTitleId, Date, Type:=1)
For Each birthCell In ws.Range("A2:A" & lastRow)
Set resultCell = ws.Cells(birthCell.Row, "B")
If IsDate(birthCell.Value) Then
resultCell.Value = AgeYMD(CDate(birthCell.Value), refDate)
Else
resultCell.Value = "Invalid date"
End If
Next birthCell
End Sub2. Click the 
Run button to execute. You will be prompted to enter a reference date (such as today's date). The macro will then write the calculated age for each row in column B, next to each date of birth in column A.
Tips: Ensure column A contains valid dates, with the first row as the header (starting from A2). If the results show "Invalid date", check the formatting in your source column.
This VBA automation provides efficiency in large or regularly updated datasets, but it requires macro-enabled files and trust center settings to be properly configured. If you prefer not to use code, consider Power Query as a no-code alternative.
Other Built-in Excel Methods - Use Power Query to calculate age by transforming data
Power Query offers a user-friendly, no-code approach for calculating age directly within Excel's modern interface. By loading your table of birth dates into Power Query, you can add a custom column to compute ages in years, months, and days—all without formulas or manual copying. This method is highly recommended for users who work with imported data or need to transform and refresh calculations regularly.
Operation Steps:
1. Select your table of birth dates, then go to Data > From Table/Range to load your data into Power Query.
2. In the Power Query editor, select Add Column > Custom Column.
3. In the formula box, enter the following M code to calculate age in years:
Date.Year(DateTime.LocalNow()) - Date.Year([DateOfBirth])Replace DateOfBirth with your column header as needed.
To calculate a more detailed breakdown (years, months, and days), you can add separate columns using:
let
Today = Date.From(DateTime.LocalNow()),
Birth = Date.From([Birthday]), Years = Date.Year(Today) - Date.Year(Birth) -
(if Date.AddYears(Birth, Date.Year(Today)-Date.Year(Birth)) > Today then 1 else 0),
AnchorY = Date.AddYears(Birth, Years),
mDiff = (Date.Year(Today)-Date.Year(AnchorY))*12 + (Date.Month(Today)-Date.Month(AnchorY)),
Months = if Date.AddMonths(AnchorY, mDiff) > Today then mDiff-1 else mDiff,
AnchorM = Date.AddMonths(AnchorY, Months),
Days = Duration.Days(Today - AnchorM)
in
Text.From(Years) & " years " & Text.From(Months) & " months " & Text.From(Days) & " days"
4. Click OK. This approach produces a column for age, or years, months, and days based on the code you used.
4. Then Close & Load to return the transformed data to your worksheet.
Notes and Troubleshooting:
- Power Query handles large sets of data efficiently and recalculates automatically when you refresh.
- If your date of birth column includes invalid or empty entries, verify data cleanliness before creating custom columns.
- Be sure to review the column names and adjust the formulas accordingly.
Power Query is ideal for users looking for a dynamic and reusable solution without writing Excel formulas or macros. However, it may not be available in Excel versions older than 2016 or Excel Online (depending on feature set).
Troubleshooting and Suggestions:
Regardless of which age calculation method you choose, always confirm that your source cells are formatted as Date, and double-check the logic if you encounter unexpected results (such as negative ages or error values). When using formulas, static reference dates will not update over time, so use =TODAY() to keep ages current. VBA and Power Query solutions are more suitable for advanced, large-scale, or regularly refreshed reports. Carefully assess your needs to select the most effective approach for your workflow. Always save your file before applying macros or extensive transformations, and, if necessary, keep a backup to avoid data loss or unintended changes.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in