Need to create a project schedule? Try our Gantt Chart for Excel!

Use DATEDIF to Calculate Age in Excel

Use DATEDIF to Calculate Age in Excel

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.

Syntax: 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")

Calculate Age in Excel Using DATEDIF - Example

NOTES

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 AgesBirthday Calendar with Ages

See 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

Calculate Age in Decimal Years - Example

NOTE

[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)

Calculate Age in Decimal Years - Example

Caution

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
Caution

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"
NOTE

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.

Calculate Age Using the 30-Day Method

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)
NOTE

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)
NOTE

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)
NOTE

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) 
NOTE

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)
NOTES

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")
NOTE

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)

Calculate Age Using the 8870 Technique

NOTE

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.

Related Content (may include ads)

Comments

4 comments… add one
  • 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).

    Reply
  • 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?

    Reply
    • @Jason. Here is one solution: =years & IF(years=1,” year, “,” years, “) & months & IF(months=1,” month, “,” months, “) & days & IF(days=1,” day”,” days”)

      Reply

Leave a Comment (comments are manually approved)

Your Name will be displayed along with your comment. Your Email and IP address are stored and used to identify/prevent spam, but are not shared.

Cancel