- The DATEDIF formula for age in years
- The units DATEDIF understands
- Why DATEDIF doesn't appear in the function list
- Other ways Microsoft shows to work out age
- Ages at a fixed date instead of today
- When the result looks like a date
- Putting ages to work
- Working out how long stock or equipment has been around
- My verdict
- Frequently asked questions
To calculate age in Excel, put the date of birth in A2 and use =DATEDIF(A2, TODAY(), "Y"). It returns the number of complete years between the birth date and today, so it only ticks up on the birthday itself. Swap "Y" for "M" to get the age in complete months.
Ages crop up more often than you'd think on a small business sheet: members' dates of birth for an age-restricted class, the date a client first signed up, how long a piece of equipment has been in service. All of them are the same calculation, the gap between a start date and today, and Microsoft documents several ways to do it. One of them is accurate to the birthday, which is the one you'll usually want.
The DATEDIF formula for age in years
- Put the date of birth in A2, as a real date rather than text.
- In B2, type =DATEDIF(A2, TODAY(), "Y") and press Enter.
- Copy the formula down for the rest of the list.
Microsoft says the Y unit returns the number of complete years in the period. That's how age works in real life: you're still the younger age until the birthday arrives. Because TODAY() updates every time the workbook recalculates, the ages stay current without you touching them.
The units DATEDIF understands
| Unit | Returns | Example use |
|---|---|---|
| "Y" | Complete years | Age of a member |
| "M" | Complete months | How long a client has been with you |
| "D" | Number of days | How long ago an order was placed |
| "YM" | Months, ignoring years | The months part of years and months |
To show something like 7 years, 3 months, combine two: =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months".
Why DATEDIF doesn't appear in the function list
This is the catch. Microsoft says Excel includes DATEDIF to support older workbooks from Lotus 1-2-3, which is why it doesn't pop up as you type and isn't in the Insert Function list. It still works; you just have to type it in full.
Microsoft also warns that DATEDIF can calculate incorrect results in some situations, and specifically recommends against the MD unit, which can return a negative number, a zero or an inaccurate result. Stick to Y, M, D and YM and you'll be fine. And if the start date is later than the end date, Microsoft says you'll get a #NUM! error, which usually means a date of birth has been typed in the wrong column or the wrong format.
Other ways Microsoft shows to work out age
- =YEAR(NOW())-YEAR(A2) subtracts the birth year from this calendar year. It's quick, but it counts a birthday that hasn't happened yet, so it can be one year too high.
- =YEARFRAC(A2, TODAY()) gives age as a decimal rather than whole years, which is handy for averages.
- =(TODAY()-A2)/365.25 divides the days by the average year length; Microsoft uses 365.25 to allow for leap years.
- =DAYS(TODAY(), A2) gives the age in days.
For a membership list or anything with an age limit, I'd always use DATEDIF with Y, because it's the only one of these that's exact to the day.
Ages at a fixed date instead of today
Sometimes you need the age on a particular day, such as the start date of a course, rather than today. Put that date in a cell, say C1, and use it instead of TODAY(): =DATEDIF(A2, C1, "Y"). Every row then shows the age on the course date, and it won't change as the weeks go by.
When the result looks like a date
If B2 shows something odd like a date from long ago rather than a number, the cell has picked up a date format. Microsoft's advice for the age formulas is to format the cell as a number or General. Select it, press Ctrl+1 and choose General, and the age appears.
If you get #VALUE! instead, the birth date is probably stored as text. Retype it as a date, or check whether the list came from a form or export that saved dates as text.
Putting ages to work
Once you have ages, you can count how many members fall into each age group with COUNTIFS, or filter for anyone under a limit. To work out an average age per class, divide the total by the count; my guide to dividing in Excel covers that. For an average age across a class, my guide to calculating the mean in Excel covers AVERAGE and its quirks.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Working out how long stock or equipment has been around
The same formula works for things as well as people. If column A holds the date each piece of equipment was bought into service, =DATEDIF(A2, TODAY(), "M") tells you how many complete months it's been in use. For stock with a received date, the D unit gives you days on the shelf, which helps you spot slow-moving lines. Sort the result column largest to smallest and the oldest items rise to the top, ready for a clear-out or a reorder decision.
I like having one age column like this on any list where time matters. It does the mental arithmetic for you every time you open the file.
My verdict
Use =DATEDIF(A2, TODAY(), "Y") for age in whole years, and type it in full because Excel won't suggest it. Avoid the MD unit, check your dates are real dates, and set the result cell to General. That's all you need for a membership list, a staff record or an equipment log.
Frequently asked questions
What is the formula to calculate age in Excel?
=DATEDIF(A2, TODAY(), "Y"), where A2 holds the date of birth. It returns complete years.
Why can't I find DATEDIF in Excel?
Microsoft keeps it for compatibility with older Lotus 1-2-3 workbooks, so it isn't suggested. Type it in full and it works.
How do I calculate age in years and months?
Join two DATEDIF formulas, one with "Y" and one with "YM".
Why is my age formula showing #NUM!?
The start date is later than the end date, often because a date has been typed incorrectly.
Why does my age show as a date?
The cell is formatted as a date. Change it to General or Number.
