- TIPS & TRICKS/
- Calculate Age from Date of Birth in Excel: Free Staff-List Exercise/
- TIPS & TRICKS/
- Calculate Age from Date of Birth in Excel: Free Staff-List Exercise/
Calculate Age from Date of Birth in Excel: Free Staff-List Exercise
To calculate age from a date of birth in Excel, put the date of birth in B2 and enter this formula in C2:
=DATEDIF(B2,TODAY(),"Y")The "Y" unit returns completed years. In this exercise, you will apply the formula to a small staff list, fill it down and test the dates most likely to reveal a mistake.
Download the free staff-list workbookWhat you will build
The workbook contains employee names, dates of birth, current ages, ages at a fixed reporting date and a check column. The sample data is fictional and includes a leap-day birthday, a birthday today, a birthday tomorrow and an invalid future date.
Step 1: Enter the current-age formula
Open the workbook and select C2 beside the first date of birth. Enter the formula below and press Enter.
=DATEDIF(B2,TODAY(),"Y")DATEDIF compares the date in B2 with today. The "Y" unit counts only complete years, so it does not round someone up before their birthday.
Step 2: Fill the formula down
Select C2, then drag the fill handle down the Age column. Excel changes B2 to B3, B4 and the next row automatically. Check that each valid date returns a whole number.
Step 3: Guard against blanks and future dates
A staff export can contain blank cells or an incorrect future date. Use this version when you want invalid input to return a blank instead of an error or negative-looking result:
=IF(OR(B2="",B2>TODAY()),"",DATEDIF(B2,TODAY(),"Y"))The workbook labels its future-date row so you can compare the guarded result with the source data. Correct the source date rather than treating it as a valid age.
Step 4: Calculate age at a fixed reporting date
TODAY() changes whenever the workbook recalculates. For payroll, audit or eligibility work that must reproduce the same result later, replace TODAY() with the reporting date:
=DATEDIF(B2,DATE(2026,12,31),"Y")Change the year, month and day inside DATE to match your reporting cut-off. Keeping the cut-off inside the formula makes the calculation easy to audit.
Step 5: Test the edge cases
- Birthday today: the age should increase on the birthday, not the day before.
- Birthday tomorrow: the result should remain one year lower until tomorrow.
- 29 February birthday: check the result around the birthday in both leap and non-leap years.
- Future date: the guarded formula should return a blank.
- Date stored as text: correct the imported value before filling the formula down.
Why dob sometimes fails
dob is not a built-in Excel reference. A formula such as =DATEDIF(dob,TODAY(),"Y") works only if somebody has already created a named range called dob. B2 is a normal cell reference that you can paste into a clean worksheet.
YEARFRAC alternative
YEARFRAC can calculate the fraction of a year between two dates. Wrap it in INT when you need completed years:
=INT(YEARFRAC(B2,TODAY(),1))Compare this result with the leap-day row before adopting it. DATEDIF with the "Y" unit states the requirement, completed years, more directly, so it is the primary method in this exercise.
Your completed result
- Every valid date of birth returns an age in completed years.
- The future date returns a blank when you use the guarded formula.
- The fixed-date column remains stable when the workbook is opened on another day.
- The leap-day, birthday-today and birthday-tomorrow rows behave as expected.
Keep real employee data out of practice files
Dates of birth are personal data. Use fictional records while learning, keep only the fields needed for the calculation and do not circulate a real staff list as a training workbook.
Read the formula explanation
The companion guide explains DATEDIF, fixed dates, YEARFRAC and the common input errors in more detail.
Read how to calculate age in ExcelPractise more Excel fundamentals
Excel Introduction covers cell references, formulas, formatting and fill handles through a wider set of workplace exercises.
View Excel IntroductionCourses related to this article
Ready to train your team?
Tell us what you need and we'll come back to you with a detailed quote.
Get a quote for your teamRelated Articles
Tips & TricksHow to Calculate Age in Excel: DATEDIF Formula and Examples
Use a copy-and-paste DATEDIF formula to calculate age from a date of birth in Excel. This guide also covers fixed reporting dates, YEARFRAC and the input checks that prevent bad results.
Insights for modern teams
Stay ahead with our latest learning trends, tools, and success stories. Enter your email address below to receive updates from us.
