The Best Way to Calculate Age in Excel from DOB

Calculating age from a date of birth in Excel is a common task in many workplaces—whether you’re managing employee records, student data, or personal information. Thankfully, Excel makes it easy to determine someone’s current age or age on a specific date using simple formulas. From basic age in years to detailed outputs in years, months, and days, there are multiple methods to choose from. In this guide, we’ll explore the best way to calculate age in Excel from DOB using functions like YEARFRAC and DATEDIF. Plus, we’ll introduce a handy online Age Calculator for instant results without formulas.

How to Calculate Current Age from Date of Birth in Excel Using YEARFRAC

To calculate a person’s current age from their date of birth in Excel, the YEARFRAC function is one of the most effective options. It determines the number of years between two dates, including partial years. To get the age in whole years, you can use the TRUNC function to remove the decimal part. Here’s how to do it:

• Open your Excel sheet and select the cell where you want the age to appear.
• Type =TRUNC(YEARFRAC( into the cell.
• Click the cell that contains the person’s date of birth.
• After that, type ,TODAY())) and press Enter.
• Excel will now display the person’s age in complete years.
• To apply this formula to other rows, drag the fill handle (small square at the bottom-right corner of the cell) down through the column.
• If your dates of birth are in column B, your final formula will be: =TRUNC(YEARFRAC(B2, TODAY()))

How to Calculate Current Age in Years and Months in Excel

If you want to display a person’s age more precisely in years and months, you can use the DATEDIF function in Excel. This method calculates the difference between the date of birth and today’s date, separating the result into full years and additional months. To do this:

• Select the cell where you want the age to show.
• Enter the formula: =DATEDIF(
• Click on the cell that contains the date of birth.
• Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
• Click the same date of birth cell again.
• Type ,TODAY(),”YM”) & ” Months” and press Enter.
• The result will display the person’s age in years and months.
• You can drag the fill handle down to apply this formula to other rows.
• If the birthdates are in column B, use the formula: =DATEDIF(B2,TODAY(),”Y”) & ” Years and ” & DATEDIF(B2,TODAY(),”YM”) & ” Months”

How to Calculate Age in Years, Months, and Days Using DATEDIF in Excel

To display a person’s full age with precise details, including years, months, and days, Excel’s DATEDIF function can be used in combination. This method gives a clear breakdown of someone’s age by calculating each part separately. Follow these steps:

• Select the cell where you want the result to appear.
• Enter: =DATEDIF(
• Click the cell containing the date of birth.
• Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
• Select the date of birth cell again.
• Type ,TODAY(),”YM”) & ” Months and ” & DATEDIF(
• Click the same date cell one more time.
• Type ,TODAY(),”MD”) & ” Days” and press Enter.
• The full age will be shown in the format: Years, Months, and Days.
• Use the fill handle to copy the formula for additional rows.
• Example formula (assuming DOB is in cell B2): =DATEDIF(B2,TODAY(),”Y”) & ” Years and ” & DATEDIF(B2,TODAY(),”YM”) & ” Months and ” & DATEDIF(B2,TODAY(),”MD”) & ” Days”

How to Calculate Age on a Specific Date in Excel

Instead of calculating age as of today, you can determine how old someone will be on a particular future or past date by using a custom date in your formula. This is useful for planning purposes, such as checking age on an event date or application deadline. Here’s how to do it:

• Click the cell where the age should be displayed.
• Type =TRUNC(YEARFRAC(
• Select the cell with the date of birth.
• Add a comma, then select the cell containing the specific date you want to use.
• Close the brackets with )) and press Enter.
• The person’s age on the chosen date will be shown.
• If you change the specific date in the sheet, the age will automatically update.
• Example formula (DOB in B2, target date in C2): =TRUNC(YEARFRAC(B2,C2))

Conclusion

Calculating age from a date of birth in Excel is straightforward with the right formulas, whether you need just the years or a detailed breakdown including months and days. Functions like YEARFRAC and DATEDIF provide flexible options to suit different needs, including calculating age as of today or any specific date. By using these methods, you can efficiently manage age data for various applications without manual calculations. With this guide, you now have all the tools to accurately determine age in Excel with ease.