Calculating a person’s age in Excel is useful for data analysis, managing customer records, employee data, and more. With a few simple formulas, you can quickly get someone’s age in years, months, or even days based on their date of birth. In this guide, we’ll cover step-by-step how to calculate current and future age from date of birth in Excel.
Calculate Current Age in Years
To calculate the current age in completed years from the date of birth, the easiest method is using the YEARFRAC formula.
Here is the syntax:
=TRUNC(YEARFRAC(date_of_birth, TODAY()))
This returns the total years between the date of birth and today’s date. The TRUNC function removes any partial year decimal values, giving just the completed age in years.
For example:
Select a cell where you want the age to appear
then place the following formula in it
=TRUNC(YEARFRAC(B2, TODAY()))
Where B2 contains birth date.
Hit enter and you will instantly get your results
You can quickly apply this to multiple cells by dragging the formula down.
The results will look like this:
Calculate Age in Years and Months
For a more precise age calculation, you can get age in both years and months using this formula:
=DATEDIF(date_of_birth, TODAY(), “Y”) & ” Years and ” & DATEDIF(date_of_birth, TODAY(), “YM”) & ” Months”
This uses DATEDIF twice – first to get years between dates, then again to get months. The & text concatenation gives a result like:
42 Years and 5 Months
Calculate Age Down to Days
To calculate age all the way down to the days, use this formula:
=DATEDIF(date_of_birth, TODAY(), “Y”) & ” Years, ” & DATEDIF(date_of_birth, TODAY(), “YM”) & ” Months”
DATEDIF is used two times, for years and months.
The end result looks like:
43 Years, 5 Months
Calculate Age on a Specific Date
The above formulas all calculate the current age based on today’s date. You can also calculate age on any future or past date like this:
=DATEDIF(date_of_birth, custom_date, “Y”)
Just replace TODAY() with any date to get the age on that day. You can reference a cell with a date value or type the date in directly.
This allows you to get someone’s age at some future milestone date like the next birthday, calculate expiration dates based on birthdate, and more.
Using Age Calculations in Excel
There are many uses for these age calculations in Excel:
- Calculate customer or employee ages for records
- Segment data by age ranges in pivot tables
- Calculate age when a future date is reached
- Determine if someone is under a certain age
- Calculate expirations relative to birth dates
- Analyze metrics by age groups over time
- Identify patterns across different age brackets
The DATEDIF and YEARFRAC formulas give you the flexibility to calculate age for any use case.
Formatting the Results
Since the age result is concatenated text, you can format it as desired:
- Add additional text or symbols
- Change cell formatting like colors
- Adjust column width to fit ages
- Left, center or right align the results
- Wrap text or merge cells as needed
Make sure ages are clear and easy to understand at a glance.
Updating Ages with New Dates
As time passes, you’ll need to update the ages. Rather than recreating formulas, simply change the end date argument:
- Replace TODAY() with TODAY()+1 to add a day
- Reference a cell with a variable date value
- Change the date with Find/Replace
Recalculating all formulas is not required. Just update the key date used across your workbook.
Limitations to Be Aware Of
A couple of limitations to note with age calculations in Excel:
- Results may not match the exact time alive. Use days precision if critical.
- Formulas become text after concatenation so don’t support math.
- Date formatting can cause errors. Use consistent YYYY-MM-DD format.
But for most general age calculation purposes, these Excel methods work well.
In Summary
Calculating age from date of birth is a snap in Excel using the DATEDIF, YEARFRAC, and TODAY functions. This allows you to get precise current or future ages in years, months, and days for effective data analysis. With a single formula applied to a column of birthdates, you can quickly generate ages across any dataset.
