OmniDocCalculate Age From a Birthdate in Excel
Learn an Excel date formula that returns whole years of age from a birthdate, with fixed and live reference dates, plus checks to confirm the results.
Subtracting one year from another by hand gives the wrong age for anyone whose birthday has not yet arrived in the current year. A roster, membership list or survey with hundreds of birthdates needs a formula that handles that check for every row. If you have been searching for excel date formulas age, the answer is a single formula that returns whole years of age from a birthdate.
This guide is for anyone who keeps birthdates in a spreadsheet and needs ages that update correctly. It covers the formula for live ages, the formula for ages on a fixed date, and the checks that catch the common mistakes. You will also see how to handle a list that starts as a PDF table.
The short version is simple, and the steps below show how to adapt it to your own sheet.
How do you calculate age with Excel date formulas? The short answer
Use DATEDIF with the birthdate as the start date, the reference date as the end date, and "Y" as the unit, which returns completed years.
| Your situation | What to do | Why |
|---|---|---|
| You need the age as of today | =DATEDIF(A2,TODAY(),"Y") | TODAY() uses the current date, so the result changes each time the file is recalculated. |
| You need the age on a report cutoff date | Put the cutoff in a cell such as D1, then use =DATEDIF(A2,$D$1,"Y") | The result stays the same no matter when you open the file. |
| The list has birthdates stored as text | Convert the cells to real dates before you apply the formula | Formulas cannot calculate with text that only looks like a date. |
| Some rows are blank | Wrap the formula in IF, such as =IF(A2="","",DATEDIF(A2,$D$1,"Y")) | Blank rows show nothing instead of an error. |
Before you start
- Confirm the birthdates are real dates. Excel stores dates as numbers, so text that looks like a date will return errors or wrong results. A quick test is to type a date into an empty cell and compare its alignment with your birthdate column; real dates normally align the same way across the column.
- Check the day and month order. A value such as 04/05/1990 can mean April 5 or May 4 depending on the regional settings of the file. Mixed order produces ages that are off by a year or more.
- Decide whether you need a live or fixed reference date. TODAY() is convenient for a dashboard, but a report printed in March should show ages as of the report date, not the date someone reopens the file.
- Know your cell layout. The examples assume birthdates start in A2 and that any fixed reference date is in D1. Adjust the references to match your sheet.
- Plan for a PDF source, if you have one. A formula cannot read a table locked inside a PDF. If your birthdates come from a PDF, export the table to Excel first, as described in the steps below.
Calculating age from a birthdate in Excel, step by step
- Enter the birthdates in column A. Start at A2 and type each birthdate as a real date, one per row. Leave the header in A1.
- Type the live-age formula in B2. Enter =DATEDIF(A2,TODAY(),"Y"). DATEDIF counts the complete intervals between two dates, and the "Y" unit returns whole years only, so someone whose birthday is next week is still counted at their current age.
- Press Enter and check the number. The result should be a whole number. If you see an error, go back to the Before you start checks and confirm A2 holds a real date.
- Fill the formula down column B. Copy B2 to the rest of the list. Excel adjusts the row number in each copy, so B3 refers to A3, B4 to A4, and so on.
- Add a fixed reference date for reports. Type the cutoff date in D1, such as the date you are reporting on. In B2, use =DATEDIF(A2,$D$1,"Y") instead. The dollar signs lock the reference to D1 when you copy the formula down, so every row uses the same date.
- Add months if you need a fuller age. To show years and remaining months, use =DATEDIF(A2,$D$1,"Y")&" years, "&DATEDIF(A2,$D$1,"YM")&" months". The "YM" unit returns the months left over after the completed years.
- Start from a PDF table, if needed. If the birthdates are in a PDF, open the file in the OmniDoc desktop client, use the PDF editor's export option to save it as an Excel file, and then repeat steps 1 to 6 on the exported sheet. Check the exported dates before you rely on them, since the table layout may need cleanup.
Check the result
Run these checks before you share the sheet or print the report.
| Check | How to see it | If it's wrong |
|---|---|---|
| A known age | Compare one row with an age you already know for that person | If the result is off by one, check the day and month order of the birthdate. |
| Text dates | Look for #VALUE! errors or ages that do not change as expected | Convert the cells to real dates, then fill the formula down again. |
| Birthday boundary | Choose one birthdate that falls the day after the reference date and one that falls on it | The age should rise only on the birthday. If it rises early, check that the reference cell holds the date you meant. |
| Live versus fixed | Reopen the file on another day and compare the results | Live results change with TODAY(). If the report must not change, replace TODAY() with a fixed date cell. |
| Leap-day birthdates | Check any row with a February 29 birthdate against your own calculation | Compare with the age on a date near the boundary and confirm the result matches your reporting rule. |
❓ FAQ
1. Why does my age show a decimal instead of a whole number?
You probably used YEARFRAC or a plain subtraction of dates divided by 365. DATEDIF with the "Y" unit returns only completed years, so use it for whole-year ages. If you need a decimal for a specific purpose, keep the formula you have and round it deliberately.
2. How do I get age in years and months?
Combine two DATEDIF formulas, one with "Y" for the years and one with "YM" for the leftover months. The example in step 6 shows the full formula. Keep the reference date consistent across both parts.
3. Does DATEDIF show up in the formula suggestions?
It may not appear in the suggestion list when you start typing, but you can type the full name and the formula still works. Make sure you include all three arguments, separated by commas, and put the unit in quotes.
4. How do I count the days between two dates instead?
Subtract one date from the other, such as =B2-A2, and format the result as a number. That gives the total days, which is a different measurement from whole years of age.
5. Can Excel read birthdates directly from a PDF?
No. Formulas work on cells, so the table must be in a spreadsheet first. Export the PDF table to Excel with the desktop client's PDF editor, then check the dates before you apply the formula.
📝 Summary
For most lists, the formula =DATEDIF(birthdate,reference date,"Y") gives the whole-year age you need. Use TODAY() for a live value or a fixed date cell for a report that must stay the same. Excel date formulas age calculations are only as reliable as the dates you start with, so confirm the dates are real before you trust the results.
