- TIPS & TRICKS/
- How to Calculate Age in Excel: DATEDIF Formula and Examples/
- TIPS & TRICKS/
- How to Calculate Age in Excel: DATEDIF Formula and Examples/
How to Calculate Age in Excel: DATEDIF Formula and Examples
If the date of birth is in B2, enter this formula in C2 to return the person’s age in completed years:
=DATEDIF(B2,TODAY(),"Y")B2 is a normal cell reference. The older dob example only works if the workbook already contains a named range called dob, so it cannot be pasted into a clean worksheet without extra setup.
Download the free age-calculator workbook
How to calculate age from a date of birth in Excel
- Put the date of birth in B2 and format the cell as a date.
- Select C2 and enter =DATEDIF(B2,TODAY(),"Y").
- Press Enter. Excel returns the number of complete years between the date of birth and today.
- Drag the fill handle down to apply the formula to the remaining rows.
The "Y" unit matters. It counts complete years, which means somebody who turns 40 tomorrow remains 39 today.
Formula for blanks and future dates
A production spreadsheet should not calculate an age from an empty cell or a date in the future. Use this guarded formula:
=IF(OR(B2="",B2>TODAY()),"",DATEDIF(B2,TODAY(),"Y"))Treat a future date as a source-data error. Correct the date of birth rather than converting it into a result that looks valid.
Calculate age at a fixed date
TODAY() changes whenever Excel recalculates. Replace it with a fixed date when a payroll, audit or eligibility report must give the same answer when reopened later:
=DATEDIF(B2,DATE(2026,12,31),"Y")Change the values inside DATE to your reporting cut-off. This is clearer and more reproducible than relying on the day someone happens to open the workbook.
Check leap-day birthdays and other edge cases
- 29 February: test immediately before and after the birthday in leap and non-leap years.
- Birthday today: the result should increase on the birthday.
- Birthday tomorrow: the result should not increase early.
- Blank cell: the guarded formula should return a blank.
- Future date: the guarded formula should return a blank so the source can be corrected.
- Text date: convert imported text to a real Excel date before filling the formula down.
YEARFRAC and INT alternative
YEARFRAC returns the fraction of a year between two dates. Wrap it in INT to keep only completed years, and use basis 1 so Excel counts actual days:
=INT(YEARFRAC(B2,TODAY(),1))DATEDIF with the "Y" unit is easier to read when the requirement is simply age in complete years. Whichever method you choose, test the leap-day row before using it across a large dataset.
Why Excel sometimes shows an error
- #NUM! can appear when the start date is later than the end date. Check for a future date of birth.
- #VALUE! can appear when the date is stored as text. Convert the source value to a real Excel date.
- An unexpected result can come from day/month ambiguity in imported data. Confirm the source locale before calculating.
Try the free staff-list exercise
The exercise gives you a downloadable workbook with a leap-day birthday, a birthday today, a birthday tomorrow, a fixed reporting date and an invalid future date.
Open the Excel age-calculator exerciseA note on dates of birth
Dates of birth are personal data. Use fictional records while practising, limit access to real staff data and keep only the fields needed for the calculation.
Build confidence with Excel formulas
Excel Introduction covers cell references, formulas, formatting and fill handles through practical workplace tasks.
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.
- Tailored contentBuilt around your team's work and skill levels.
- Flexible schedulingDates and times that suit your team, not ours.
- Your place or onlineDelivered at your office or virtually.
- Better value per headOne trainer, one day, your whole team.
- A dedicated trainerYour team gets all of the trainer's attention.
- Your own filesWe can use your data as the worked examples.
We reply with available dates and a price for your team. No obligation.
Frequently asked questions
If the date of birth is in B2, use =DATEDIF(B2,TODAY(),"Y") to return age in completed years.
Replace TODAY() with the reporting date. For 31 December 2026, use =DATEDIF(B2,DATE(2026,12,31),"Y").
DATEDIF returns #NUM! when the start date is later than the end date. Check whether the date of birth is in the future or the two arguments are reversed.
Wrap DATEDIF in IF and test for a blank or future date: =IF(OR(B2="",B2>TODAY()),"",DATEDIF(B2,TODAY(),"Y")).
Related Articles
Tips & TricksHow to Calculate Compound Interest in Excel
Build a simple Excel calculator to forecast investment growth with compound interest.
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.
