Calculate Age in Excel
To calculate age in Excel, the simplest method uses the DATEDIF function to count the years between a birth date and today. This guide is free with no sign-up. It also links to a q
To calculate age in Excel, the simplest method uses the DATEDIF function to count the years between a birth date and today. This guide is free with no sign-up. It also links to a quick online age calculator. You can skip the formulas whenever you prefer. Sometimes typing two dates is faster.
Excel does not show DATEDIF in its function list, but it still works. It is the cleanest way to get an age formula right. We explain the exact syntax below. Once you learn the pattern, it is fast. It takes only a few seconds to use.
You can use it for one cell or a whole column of dates. It is quick once you know the structure. Everything here is free, with no account and no ads. You can follow along as you read. You can try each step for yourself.
The DATEDIF formula
The core age formula is DATEDIF(birthdate, today, unit). The first argument is the birth date cell. The second is the end date, often the TODAY function. The third is the unit you want back. Each part has a clear job.
To get whole years, you use the unit letter Y. For example, DATEDIF(A2, TODAY(), Y) returns completed years. That single line is enough for a basic age. It is simple. It ignores the leftover months and days.
The DATEDIF function counts complete periods only. It ignores any partial year, which is exactly what an age needs. This is why it beats subtracting dates directly. A plain subtraction falls short. It gives raw days, not a tidy age.
You can point the end date at a fixed day instead of TODAY. That lets you calculate age in excel as of any chosen date. Just reference a cell holding that date. The rest of the formula stays the same. Only the end date changes.
Make sure both cells are true dates, not text. Excel treats text dates differently, which breaks the formula. Formatting the cells as dates fixes most early problems. A quick check helps. It saves a lot of later frustration.
It helps to store the birth date in its own dedicated cell before you begin. Then reference that cell in the formula rather than typing a date directly. You can reuse the same formula down a whole column of people. One clean pattern serves an entire list without any extra effort.
The TODAY function keeps every age current all on its own. Each time you open the sheet, Excel recalculates it for you. So an age column stays accurate as the weeks and months pass. You never have to go back and update those figures by hand.
Text labels make the final output far easier for anyone to read. Join the year, month, and day parts with plain words between them. The result then reads almost like an ordinary sentence. Colleagues can understand it at a glance without any explanation from you.
Formatting the source cells as genuine dates prevents most of the common errors. Excel quietly treats a text date differently, which breaks the DATEDIF function. A quick format fix usually solves the problems people run into first. It is well worth checking before you blame the formula itself.
For a quick one-off check, the online tool is genuinely faster than a spreadsheet. You skip building any formula at all and simply type two dates. The exact age appears straight away in years, months, and days. It suits a single question that never needs a saved sheet.
Combining the three units into one cell keeps a report tidy. Use the ampersand to join the years, months, and days together. The whole age then sits neatly in a single column. That small touch makes a printed or shared sheet look far more professional.
FreeUSUKCalculator.com is 100% free with no sign-up and no ads, and it has dedicated US and UK modes with the correct currency, tax rules, and units.
Years, months, days
For a full breakdown, you combine three DATEDIF calls. Use Y for years, then YM for leftover months, then MD for leftover days. Joined together, they give a precise age. This is what most people want. It mirrors a full age.
A common pattern joins them with text labels between the parts. The result reads like 26 years, 4 months, 10 days. This is the detail an age calculator excel users expect. It is clear. Anyone can read it at a glance.
The YM unit counts the months after the full years. The MD unit counts the days after the full months. Together they cover the whole gap. There is no overlap. So the three parts always add up correctly.
This detailed age formula is useful for HR and school records. It shows more than a single year figure. It is precise enough for official use. Teams like it. Many paste it into a template and reuse it every day.
You can wrap the parts in a single cell with the ampersand. That keeps the full age tidy in one place. It is handy when you print or share a sheet. A little formatting goes a long way. It makes the result look professional.
Common errors
A frequent error is quoting the unit wrongly. The unit must sit inside quotation marks, like the letter Y in quotes. Missing quotes causes a formula error. It is easy to miss. Double-check them first if Excel rejects your line.
Another slip is putting the end date before the start date. DATEDIF expects the earlier date first. Swapping them returns an error instead of an age. The order is strict. Keep the birth date first and the end date second.
A third issue is using the MD unit alone for days. On its own it can behave oddly across months. Combining it with Y and YM avoids that. The full pattern is safer. The three-part formula is the reliable choice.
If you prefer to skip formulas entirely, use our online tool. It does the same job without any spreadsheet. That is often faster for a one-off age check. You just type two dates. You then read the result at once.
Choosing between a spreadsheet formula and a quick online tool comes down to the job in front of you. For a long list of people, DATEDIF in Excel is hard to beat. For a single, fast answer, the online calculator wins on speed. Knowing both means you always have the right approach ready. Either way, the underlying age is exactly the same, so pick whichever suits your task.
Skip the spreadsheet when you just need one answer. Try it free on FreeUSUKCalculator.com: no sign-up, no ads, with US and UK modes.
Frequently Asked Questions
What formula calculates age in Excel? Use DATEDIF(birthdate, TODAY(), Y) for whole years. Combining Y, YM, and MD units gives years, months, and days.
Why can't I find DATEDIF in Excel? DATEDIF is hidden and does not appear in the function list, but it still works when you type it. The syntax must be exact.
Can I calculate age as of a past date? Yes. Replace TODAY() with a cell holding the date you want, and DATEDIF measures the age up to that day.
Is there an easier way? Yes. Our free online age calculator does the same job with no formula, no sign-up, and no ads.