To calculate age in Excel, subtract the date of birth from today and convert the result to years. The most reliable way is the DATEDIF function, which counts complete years between two dates. You can also get the age in years, months and days, the age on a particular date, the days until the next birthday, and group people into age bands. This guide shows each formula with a worked example that you can verify by hand. In the examples, the date of birth is 15 March 1990 and the date used for the calculation is 18 November 2026. It also explains why simply dividing by 365 can give a wrong answer and how to avoid common errors.
Updated November 2026
In this guide
- The quick answer
- Example data
- Method 1: DATEDIF for complete years
- Method 2: age in years, months and days
- Method 3: YEARFRAC for a decimal age
- Method 4: a simple formula without DATEDIF
- Age on a specific date
- Days, weeks and months lived
- Days until the next birthday
- Group people by age
- Calculate ages for a whole list
- Check the result with a second method
- Handle blank cells and errors
- Common mistakes
- Practice problems
- Quick recap
The quick answer
With the date of birth in cell A2, use =DATEDIF(A2,TODAY(),"Y"). It returns the number of complete years between the two dates. For a birth date of 15 March 1990 and a current date of 18 November 2026, the result is 36. The person turned 36 on 15 March 2026 and will turn 37 on 15 March 2027. Format the cell as General or Number, since it should show a plain number and not a date. The formula updates every day, because TODAY changes.
Example data
Use three people. Person one was born on 15 March 1990. Person two was born on 2 August 2005. Person three was born on 30 December 1978. The date for the calculation is 18 November 2026. The expected ages in complete years are 36, 21 and 47. We will check each formula against these numbers, and we will also find the months and days for each person.
Method 1: DATEDIF for complete years
DATEDIF takes three arguments: the start date, the end date and the unit. The unit “Y” returns complete years. For person two, =DATEDIF(A3,TODAY(),"Y") returns 21, because the person turned 21 on 2 August 2026 and will turn 22 in 2027. For person three it returns 47, because the 48th birthday is on 30 December 2026, which is still in the future. DATEDIF does not appear in the function list when you type, but it works in all versions of Excel. See our DATEDIF guide for the other units.
Method 2: age in years, months and days
To show the age as 36 years, 8 months and 3 days, combine three DATEDIF calls: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days". The unit “YM” gives the months after the complete years, and “MD” gives the days after the complete months. For 15 March 1990, the years are 36, the months from 15 March to 15 November are 8, and the days from 15 November to 18 November are 3. Person two is 21 years, 3 months and 16 days, and person three is 47 years, 10 months and 19 days.
Method 3: YEARFRAC for a decimal age
YEARFRAC returns the fraction of a year between two dates. With basis 1, which uses the actual days, =YEARFRAC(A2,TODAY(),1) returns about 36.68 for our first person. The default basis, which assumes 30-day months, returns 36.675. To get complete years from it, wrap it in INT: =INT(YEARFRAC(A2,TODAY())), which returns 36. A decimal age is useful for statistics, for example the average age of a group, or for a rate per year of age.
Method 4: a simple formula without DATEDIF
Some people use =INT((TODAY()-A2)/365.25). The number of days between 15 March 1990 and 18 November 2026 is 13,397, and 13,397 divided by 365.25 is 36.68, so INT gives 36. This works for most dates, since 365.25 averages in the leap years. But it can be off by one on or near a birthday, so DATEDIF is safer for exact ages. If you only need an estimate for a large list, this formula is fast and easy to understand.
Age on a specific date
To calculate the age on a certain date instead of today, put that date in cell B2 and use =DATEDIF(A2,B2,"Y"). For example, the age of person one on 1 January 2030 is 39, because the birthday of 15 March 2030 has not yet come. This is useful for eligibility checks, such as the age on the first day of school, or on the date of an event. Make sure that the end date is later than the start date, or DATEDIF returns a #NUM! error.
Days, weeks and months lived
The number of days lived is simply =TODAY()-A2, which gives 13,397 days for person one. Divide by 7 for weeks, which is about 1,914 weeks. For complete months, use =DATEDIF(A2,TODAY(),"M"), which gives 440 months for person one, since 36 years times 12 plus 8 months is 440. These numbers are popular for fun facts, but they are also used in health and education, where the age in months is important for young children.
Days until the next birthday
To find the next birthday, build this year’s birthday with DATE and check whether it has passed. =DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()),MONTH(A2),DAY(A2)) returns 15 March 2027 for person one, since 15 March 2026 has passed. Subtract today: =B2-TODAY() gives 117 days. For person three, the next birthday is 30 December 2026, which is 42 days away. Format the first formula as a date and the second as a number.
Group people by age
You can classify ages with IF or with a lookup table. For example =IF(C2<18,"Minor",IF(C2<65,"Adult","Senior")) uses the age in C2. With our three people, 36, 21 and 47 are all Adult. For more groups, create a table with the lower limits 0, 18, 30, 45 and 65 and the labels, and use =LOOKUP(C2,Limits,Labels). Then count the people in each group with COUNTIF, as in our COUNTIFS guide. Age groups are common in reports and surveys.
Calculate ages for a whole list
For a list of 500 people, put the dates of birth in column A and the formula in column B, then fill down. If you convert the list to a table, as explained in our table guide, the formula fills automatically for new rows. Add a column for the age group and another for the next birthday. You can then sort by age, filter by group or build a PivotTable that counts people in each band. Using one formula in the whole column keeps the results consistent, and it is much faster than typing the ages by hand and updating them every year.
Check the result with a second method
When the age matters, such as for legal or medical purposes, check the result with a second formula. For example, compare DATEDIF with =YEAR(TODAY())-YEAR(A2)-(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY()). This formula subtracts the years and takes away one if this year’s birthday has not yet happened. For 15 March 1990 on 18 November 2026, the years difference is 36, and the birthday has passed, so the result is 36 as well. If the two methods disagree, look for text dates or a wrong cell reference.
Handle blank cells and errors
If a row has no birth date, DATEDIF returns a large number, because a blank cell is treated as the date 0 in Excel. Protect against this with an IF: =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). If a date is stored as text, the formula returns a #VALUE! error, so convert it first, as described in our Text to Columns guide. If the birth date is in the future by mistake, you get #NUM!. Wrap the formula with IFERROR if you want a clean report, as in our IFERROR guide.
Common mistakes
The most common mistake is to divide the days by 365, which ignores leap years and gives errors of one year around birthdays. Another is to format the result as a date, so Excel shows a strange date in 1900. A third is to use text dates. A fourth is to forget that the “MD” unit can give odd results in some cases, so check it for dates at the end of a month. A fifth is to hard-code today’s date instead of using TODAY, so the age never updates. A sixth is to use different date formats on different computers.
Practice problems
Try these. Problem 1: the age of someone born on 1 January 2000, on 18 November 2026, is 26. Problem 2: for 15 March 1990 the age in months is 440. Problem 3: the days between 1 January 2026 and 18 November 2026 is 321. Problem 4: write a formula that shows blank when the birth date is empty. Problem 5: the age of person three on 31 December 2026 is 48.
Quick recap
Use =DATEDIF(birth,TODAY(),"Y") for the age in complete years, and add the units YM and MD for months and days. Use YEARFRAC for a decimal age and the 365.25 method only for rough estimates. Replace TODAY with another date to find the age on that date, and build the next birthday with DATE. Protect formulas against blanks and text dates, and group people with IF or LOOKUP. Check one or two results by hand every time, such as the examples in this guide.
Frequently asked questions
How do I calculate age in Excel?
Use =DATEDIF(birth_date,TODAY(),”Y”).
How do I show age in years, months and days?
Combine DATEDIF with the units Y, YM and MD.
Why not divide by 365?
Leap years make the result inaccurate near birthdays.
How do I calculate age on a specific date?
Replace TODAY() with the date in the formula.
Why does my age formula show a date?
The cell is formatted as a date, so change it to General.


