The simplest and most accurate formula to calculate age in Excel is =DATEDIF(birth_date,as_of_date,"y"). This returns the number of years rounded down. Other methods, such as =INT((end-start)/365.25) or =INT(YEARFRAC(start,end)) are not 100% correct. See below for examples and an explanation of other formulas having to do with calculating age in Excel.
To see the examples in action, download the Excel file below.
Download the Example File (CalculateAge.xlsx)
This Article (bookmarks):
Syntax for DATEDIF
DATEDIF(start_date,end_date,interval)
| Interval | Returns |
|---|---|
| "y" | Number of complete years between two dates |
| "m" | Number of complete months between two dates |
| "d" | Number of days between two dates |
| "ym" | Remaining number of whole months (derived from "y" and "m") |
| "yd" | Remaining number of days after ignoring complete years |
| "md" | Remaining number of days after Adding whole years and months to the Start Date |
Calculate Age in Years
Method 1: Use DATEDIF to Return the Number of Whole Years Between Two Dates
This is the simplest and most accurate method. Although dates cannot be earlier than 1900, they can either be date values or text. DATEDIF automatically uses DATEVALUE to convert valid date text to date values, so DATEDIF("17-Aug-1968","2017-08-17","y") would work.
=DATEDIF(birth_date,as_of_date,"y")
If you want the as_of_date to always be today's date, then use TODAY() in place of as_of_date.
DATEDIF returns 0 years for 29-Feb-2016 to 28-Feb-2017 (which is correct for most interpretations of a whole year).
Birthday Calendar with AgesSee DATEDIF in action. This template can be used to show the birthdays of your relatives on a yearly calendar, and the DATEDIF function calculates what their age will be on their birthday.
Method 2: Calculate Age in Years Without Using DATEDIF
This formula is a valid alternative to DATEDIF(start,end,"y"). It returns the same results if the inputs are valid dates after 1900 and end > start. How it works: It subtracts the years and then subtracts 1 if the month and day of the end date comes before the month and day of the start date.
=YEAR(end) - YEAR(start) - (DATE(YEAR(end),MONTH(start),DAY(start)) > end)
NOTETo force an error when start > end, wrap the function with =IF(start>end, NA(), original_formula). In Excel, the numeric value for TRUE is 1. This is why we can subtract the result of (x > y).
Method 3: Return a Decimal Number of Years Between Two Dates
The exact number of days in a year is 365.2422[1]. Using 365.25 is an approximation. To calculate age using this method, first subtract the dates to calculate the number of days, then divide by 365.2422.
=(end_date-start_date)/365.2422
[1] "Exact Number of Days in a Year" is 365.2422, according to https://pumas.gsfc.nasa.gov/files/04_21_97_1.pdf
Another common way to calculate the decimal number of years is to use the YEARFRAC function, however the result may not always be correct (e.g. 31-Jul-2012 to 30-Jul-2015).
=YEARFRAC(start_date,end_date)
Using INT or ROUNDDOWN with this method to return the number of whole years between two dates will sometimes lead to incorrect results (e.g. 28-Feb-2013 to 28-Feb-2016). Though the error is rare (< 1% of date combinations I tested), this method is not a valid substitute for DATEDIF(start,end,"y").
Calculate Age in Years, Months and Days
Ages are often represented using the format 5y 11m 3d or 5 years 11 months and 3 days. There are multiple methods for calculating the age as a combination of years, months and days, but not all methods give the same answers. The complications and differences come from how we treat months with different numbers of days in them.
For example, if you subtract a month from 31-Mar-2017, should it be 28-Feb-2017? If so, we can use EDATE(end_date,-1) or EOMONTH(end_date,-1). However, if we use the DATE(year,month-1,day) method, the result would be 3-Mar-2017. If we assume a 30-day month, then (end_date-30) results in 1-Mar-2017. The point is that there may be multiple right answers, depending on the methodology.
Method 1: Use DATEDIF to Calculate Age in Years, Months & Days
Step 1: Calculate the number of complete years
years =DATEDIF(start_date, end_date, "y")
Step 2: Calculate the remaining number of complete months after subtracting years from the end date.
months =DATEDIF(start_date, end_date, "ym")
Step 3: Calculate the remaining number of days after subtracting months from the end date.
days =DATE( YEAR(end), MONTH(end) - DATEDIF(start,end,"m"), DAY(end)) - start
You might think you could use DATEDIF(start,end,"md") for this step, but there is a known bug with the "md" option that can lead to negative values in both Excel and Google Sheets (such as when using 31-Mar-2014 and 1-Mar-2020). The work-around formula =end-DATE(YEAR(end),MONTH(end,1) listed on the Microsoft Support site here and here is not correct (e.g. 31-Jan-2016 to 31-Jan-2017 returns the value 30 and it should be 0).
Step 4: Concatenate the results from steps 1-3
The formula below returns a text string that looks like 49y 11m 6d.
=years & "y " & months & "m " & days & "d"
If you want a single formula that combines steps 1-4, you can combine the formulas to get the following mega formula.
=DATEDIF(start_date, end_date, "y") & "y " & DATEDIF(start_date, end_date, "ym") & "m " & (DATE( YEAR(end), MONTH(end) - DATEDIF(start,end,"m"), DAY(end)) - start) & "d"
After using this method to calculate the age, you can use the DATE formula mentioned in the next section to calculate the original birthdate from the age and death date. This fact makes it a very useful methodology because the process is reversible (information is not lost).
Method 2: The 30-Day Month Technique
The 30-Day Month method is a technique I learned from doing genealogy work. It is based on the procedure of subtracting dates and ages by hand using the technique of first writing dates and ages as YYYYMMDD. In elementary school, you learned that when subtracting you can borrow from the tens or hundreds place when needed. Here, if we need to borrow from the MM place, we subtract one from MM and then add 30 days to DD. If we need to borrow from the YYYY place, we subtract one from the YYYY place and add 12 to MM.
Step 1: Subtract the days in the DD place, borrowing 30 days from the MM place if needed.
days =DAY(end)+30*(DAY(end)<DAY(start))-DAY(start)
How it works: Start with the day of the end date, then add 30 days if you need to borrow, then subtract the day of the start date. Excel may try to convert the result to a date, so change the number format back to General.
Step 2: Subtract the months in the MM place, borrowing 12 months from the YY place if needed.
months =MONTH(end) - 1*(DAY(end)<DAY(start)) + 12*((MONTH(end) - 1*(DAY(end)<DAY(start)))<MONTH(start)) - MONTH(start)
This formula is a valid alternative to DATEDIF(start,end,"ym"). How it works: Start with the month of the end date, then subtract 1 if you had to borrow 30 days, then add 12 months if you need to borrow from the years, then subtract the month of the start date.
Step 3: Subtract the years in the YYYY place.
years =YEAR(end) - 1*( (MONTH(end)-1*(DAY(end)<DAY(start))) < MONTH(start) ) - YEAR(start)
This formula is a valid alternative to DATEDIF(start,end,"y"). How it works: Start with the year of the end date, then subtract 1 if you previously had to borrow 12 months, then subtract the year of the start date.
Method 2 results in a different age than Method 1 about 32% of the time, but only by 2 days at most. Turns out that both methods return the exact same number of years and months. It is only the number of days that may be different.
Calculate the Birthdate if you Know the Death Date and Age
Use DATE to subtract a combination of years, months and days from a date. Whether the answer is correct depends upon the methodology used to calculate the age.
=DATE(YEAR(end_date)-years, MONTH(end_date)-months, DAY(end_date)-days)
If you use Method 1 above to calculate the age in years, months and days, this formula will return the original start date 100% of the time.
Calculate the Death Date if you Know the Birth Date and Age
Use DATE to add a combination of years, months and days to a date. Whether the answer is correct depends upon the methodology used to calculate the age.
=DATE(YEAR(start_date)+years, MONTH(start_date)+months, DAY(start_date)+days)
If you use Method 1 above to calculate the age, this formula will return the original end date about 75% of the time (can be off by up to 3 days).
It turns out that if you are okay with the DATEDIF "md" option leading to negative values for days, and use DATEDIF(start,end,"md") in place of Step #3, this formula will return the original end date 100% of the time.
Calculate Age in Excel for Dates Prior to 1900
Excel Date functions only work for dates after 1/1/1900, because the date serial numbers in Excel begin with 1 on 1/1/1900. If you enter a date such as 28-Aug-2017 in Excel, it will store that as the number 42975, but if you enter a date prior to 1900, it will store the date as text. Below are a couple of methods for calculating age in Excel when working with dates prior to 1900.
Method 1: Add 2000 years then use the formulas above
For example, use the formula below to calculate the age when the birth date is 28-Aug-1803 and death date is 28-Aug-1850.
=DATEDIF("2-Jan-3803","28-Aug-3850","y")
Adding 2000 years allows the formula to handle leap years correctly. Plus, it's easy to add and subtract 2000 years in your head.
Method 2: Create a User-Defined Function based on VBA date functions
The VBA date functions can handle all valid Gregorian dates. See the article "How to Create Custom Excel Functions" for the VBA code that can calculate ages for dates earlier than 1900.
Method 3: Calculate age using the 30-Day Month Method and YYYYMMDD date values
If you enter dates as numeric values in the format YYYYMMDD, then you can use the 30-day month method (also known as the 8870 technique) to subtract the dates. After subtracting the start date from the end date, if DD > 31, then subtract 70, and if MM > 12, subtract 8800. The result will be the age in YYMMDD format. You can then use a custom number format of 0"y "00"m "00"d" to display the age.
=end - start - IF(VALUE(RIGHT(end-start,2))>31,70) - IF(VALUE(RIGHT(INT((end-start)/100),2))>12,8800)
To convert a date value (>1900) to a YYYYMMDD value, use =VALUE(YEAR(date) & TEXT(MONTH(date),"00") & TEXT(DAY(date),"00"))
You can use the following custom number format for YYMMDD age values to only display the years and months if they are not zero: [>9999]0"y "00"m "00"d";[>99]0"m "00"d";0"d"
For an age stored as a YYMMDD value, use
=VALUE(RIGHT(yymmdd,2)) to return the days, use
=VALUE(RIGHT(INT(yymmdd/100),2)) to return the months and use
=INT(yymmdd/10000) to return the years.
DATEDIF "md" Bug and Work-Around
As I mentioned above, using the "md" unit in the DATEDIF function is not always correct (both in Excel and in Google Sheets). The work-around formula recommended by Microsoft on their support page is wrong even more often. There are two methods for calculating the remaining days, shown below. Method #1 is the method I think DATEDIF uses, but it can lead to negative values that are clearly wrong. Method #2 is a valid method that never results in negative values and fits the definition of "remaining days between dates after subtracting whole months."
Method 1: Return remaining days after Adding whole months to the Start Date
The following formula matches the result of DATEDIF(start,end,"md") 100% of the time (based on extensive random sampling of dates), so I'm pretty sure that this is how DATEDIF is doing the calculation.
=end - DATE( YEAR(start), MONTH(start) + DATEDIF(start,end,"m"), DAY(start))
Why does DATEDIF return -1 or -2? Here is an example: When adding a month to 31-Jan-2015, if we used EDATE("31-Jan-2015",1), we'd get 28-Feb-2016 which is what you might be expecting, but DATEDIF does not use EDATE. It uses DATE(2015,1+1,31) which returns the date 3-Mar-2015. If the end date is 1-Mar-2015, the result will end up being -2.
Method 2: Return remaining days after Subtracting whole months from the End Date
The following formula is the correct work-around to use instead of the "md" option.
=DATE( YEAR(end), MONTH(end) - DATEDIF(start,end,"m"), DAY(end)) - start
Final Note
I purposely started this article with the simple answer of using DATEDIF(start,end,"y") to calculate age - which is probably the only thing 99% of people need to know. But, I also wanted to delve deep to address some of the intracacies of other methods. The spreadsheet I set up to compare these various methods analyzed 100's of thousands of date combinations. That is how I came up with examples where some of these methods produce errors and how I checked to make sure the alternative formulas are (or are not) 100% correct. The YEARFRAC and division by 365.2422 methods result in errors less than 0.2% of the time, but that may still be an issue if you are expecting 100% accuracy.
If you have any questions pertaining to calculating age or these particular formulas, or see any typos, you are welcome to comment below or email me.
References
- DATEDIF Function support.office.com - The official documentation of the DATEDIF function (the page linked to when clicking on the DATEDIF syntax helper text in Excel). Caution: contains an incorrect solution for an alternative to the "md" option.
- Get Age From Birthday exceljet.net - Shows the YEARFRAC method.
- The DATEDIF Worksheet Function cpearson.com - Discusses an issue with the "yd" option having to do with leap year. I have not yet figured out a substitute formula, although DATE(YEAR(end)-DATEDIF(start,end,"y"),MONTH(end),DAY(end))-start matches the DATEDIF(start,end,"yd") formula about 98% of the time.
Comments
Jon, this was really helpful, thank you. In the formula for showing years, months and days, I can see how you can use =years & “years, ” & months & “months, and ” & days & “days” …. but how do you make it say “1 day” instead of “1 days” (and the same for months and years) … is there an easy way to do that?
@Jason. Here is one solution: =years & IF(years=1,” year, “,” years, “) & months & IF(months=1,” month, “,” months, “) & days & IF(days=1,” day”,” days”)
Perhaps I missed it among your various examples above, but I’ve been trying to find a formula that will show me what age someone will turn this year. I just want to have a spreadsheet that shows all of my extended family members, use their birthdates, and see what they’ll be turning this year. Right now I’m using =DATEDIF(C3,NOW(),”y”), but that just shows me what age they are now–not what they will be (for those who have birthdays later on this year).
@Ryan, Check out the “Birthday Calendar with Ages” template on the following page: https://www.vertex42.com/calendars/birthday-calendar.html
The formula to calculate the age this year would be =DATEDIF(birthdate,DATE(YEAR(TODAY()),MONTH(birthdate),DAY(birthdate)),”y”)
How can I calculate age in months and days only, for infant, toddler and preschool children’s assessments?
@Eme … Follow the method described in “Calculate Age in Years, Months, and Days” to calculate the number of days using Step 3. To calculate the number of complete months, use the “m” interval with the DATEDIF function.
i need to calculatethe age
column A23 date of birth (1/1/2016)
column B23 THE END OF THIS YEARS DATE (12/31/2020)
I USE THE DATEDIF FORMULAR BUT ALWAYS GET BROKEN FORMULAR ERROR.
Question1: what must the number formular be : short date, general or text? Column A
Question2: what must the number formular be : short date, general or text? Column B
Question3: what must the number formular be : short date, general, number or text? Column C
First of all great work. Could you also please advice how to include end day as well in the calculation for the MD alternative formula?
Question 1: I have a cell on my spreadsheet that should show someone’s age (it should show either current age or age at death, whichever applies). I am trying to find a formula that would calculate these scenarios. I believe it should be an if/then type statement, because whatever the answer, it should populate this one cell. My spreadsheet currently has one cell with the today’s date (DATE), one for the date of birth (DOB) and a cell which would only be populated if the person is deceased (DOD). So, theoretically the age would be found using combinations of those three cells. Question 2: How do I get the “age” cell to show the correct age – 19.8 years rounds up to 20, but that person is actually only 19. Thanks
Thanks a lot. This helped me a lot.
The simple DATEDIF formula to calculate rounded years worked for me when calculating the age between birth date and the date of a specific event. Unfortunately, the birthdate is not available in all records. How do I write the formula to ignore the missing data?
Thank you!
Fantastic article, the very last section (Method #2) to obtain the days has resolved my issue that other methods did not match when doing a reverse calculation (subtracting years months days from a date). Many thanks!
What is the function to find the DATE a person turns 18 if I have their birthdate?
The EDATE function easily adds months to a date, so you could try this: =EDATE(birthdate,12*18)