{"id":3404,"date":"2017-09-01T12:31:09","date_gmt":"2017-09-01T18:31:09","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3404"},"modified":"2021-11-01T10:06:02","modified_gmt":"2021-11-01T16:06:02","slug":"use-datedif-to-calculate-age-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/use-datedif-to-calculate-age-in-excel.html","title":{"rendered":"Use DATEDIF to Calculate Age in Excel"},"content":{"rendered":"<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/use-datedif-to-calculate-age-in-excel.png\" alt=\"Use DATEDIF to Calculate Age in Excel\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/use-datedif-to-calculate-age-in-excel.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>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.<\/p>\n<div class=\"clear\"><\/div>\n<p>To see the examples in action, download the Excel file below.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/CalculateAge.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'CalculateAge.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (CalculateAge.xlsx)<\/p>\n<div class=\"contents\">\n<p>This Article (bookmarks):<\/p>\n<ul>\n<li><a href=\"#age-in-years\">Age in years (3 methods)<\/a><\/li>\n<li><a href=\"#age-in-ymd\">Age in years, months and days (2 methods)<\/a><\/li>\n<li><a href=\"#calculate-birthdate\">Calculate the birthdate from the age<\/a><\/li>\n<li><a href=\"#calculate-deathdate\">Calculate the death date from the age<\/a><\/li>\n<li><a href=\"#pre-1900\">Age for dates before 1900 (3 methods)<\/a><\/li>\n<li><a href=\"#datedif-md-bug\">DATEDIF \"md\" bug and the work-around that works<\/a><\/li>\n<\/ul>\n<\/div>\n<h2>Syntax for DATEDIF<\/h2>\n<p style=\"font-size:1.2em;\"><b>DATEDIF<\/b>(<i>start_date<\/i>,<i>end_date<\/i>,<i>interval<\/i>)<\/p>\n<table class=\"border\">\n<tbody>\n<tr>\n<th>Interval<\/th>\n<th>Returns<\/th>\n<\/tr>\n<tr>\n<td>\"y\"<\/td>\n<td>Number of complete <b>years<\/b> between two dates<\/td>\n<\/tr>\n<tr>\n<td>\"m\"<\/td>\n<td>Number of complete <b>months<\/b> between two dates<\/td>\n<\/tr>\n<tr>\n<td>\"d\"<\/td>\n<td>Number of <b>days<\/b> between two dates<\/td>\n<\/tr>\n<tr>\n<td>\"ym\"<\/td>\n<td>Remaining number of whole months (derived from \"y\" and \"m\")<\/td>\n<\/tr>\n<tr>\n<td>\"yd\"<\/td>\n<td>Remaining number of days after ignoring complete years<\/td>\n<\/tr>\n<tr>\n<td>\"md\"<\/td>\n<td>Remaining number of days after Adding whole years and months to the Start Date<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 id=\"age-in-years\">Calculate Age in Years<\/h2>\n<h3>Method 1: Use DATEDIF to Return the Number of Whole Years Between Two Dates<\/h3>\n<p>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.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\"><b>DATEDIF<\/b><\/span>(<span class=\"arg\">birth_date<\/span>,<span class=\"arg\">as_of_date<\/span>,\"y\")<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/calculate-age-in-years-using-datedif.png\" alt=\"Calculate Age in Excel Using DATEDIF - Example\" class=\"border imgshadow\"><\/p>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTES<\/div>\n<p>If you want the <i>as_of_date<\/i> to always be today's date, then use TODAY() in place of <i>as_of_date<\/i>.<\/p>\n<p>DATEDIF returns 0 years for 29-Feb-2016 to 28-Feb-2017 (which is correct for most interpretations of a whole year).<\/p>\n<\/div>\n<div class=\"template-feature\">\n<p><a href=\"https:\/\/www.vertex42.com\/calendars\/birthday-calendar.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/calendars\/images\/birthday-calendar-with-ages_180.png\" alt=\"Birthday Calendar with Ages\" width=\"139\" height=\"180\" style=\"float:left;margin-right:1em;\" class=\"imgshadow\"><b>Birthday Calendar with Ages<\/b><\/a><\/p>\n<p>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.<\/p>\n<div class=\"clear\"><\/div>\n<\/div>\n<h3>Method 2: Calculate Age in Years Without Using DATEDIF<\/h3>\n<p>This formula is a valid alternative to DATEDIF(<i>start<\/i>,<i>end<\/i>,\"y\"). It returns the same results if the inputs are valid dates after 1900 and <i>end<\/i> &gt; <i>start<\/i>. 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.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>) - <span class=\"function\">YEAR<\/span>(<span class=\"arg\">start<\/span>) - <b>(<\/b><span class=\"function\">DATE<\/span>(<span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>),<span class=\"function\">MONTH<\/span>(<span class=\"arg\">start<\/span>),<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>)) &gt; <span class=\"arg\">end<\/span><b>)<\/b><\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span>To force an error when <i>start<\/i> > <i>end<\/i>, wrap the function with =IF(<i>start<\/i>&gt;<i>end<\/i>, NA(), <i>original_formula<\/i>). In Excel, the numeric value for TRUE is 1. This is why we can subtract the result of (x > y).<!-- If it is important enough to count 2\/29\/2016 to 2\/28\/2017 as a whole year, you can add the following to this formula: +IF(AND(MONTH(<i>start<\/i>)=2, DAY(<i>start<\/i>)=29, MONTH(<i>end<\/i>)=2, DAY(<i>end<\/i>)=28),1,0) --><\/p>\n<\/div>\n<h3>Method 3: Return a Decimal Number of Years Between Two Dates<\/h3>\n<p>The exact number of days in a year is 365.2422<sup>[1]<\/sup>. 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.<\/p>\n<pre class=\"XLformula\">=(<span class=\"arg\">end_date<\/span>-<span class=\"arg\">start_date<\/span>)\/365.2422<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/calculate-age-in-decimal-years.png\" alt=\"Calculate Age in Decimal Years - Example\" class=\"border imgshadow\"><\/p>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>[1] \"Exact Number of Days in a Year\" is 365.2422, according to https:\/\/pumas.gsfc.nasa.gov\/files\/04_21_97_1.pdf<\/p>\n<\/div>\n<p>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).<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">YEARFRAC<\/span>(<span class=\"arg\">start_date<\/span>,<span class=\"arg\">end_date<\/span>)<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/calculate-age-using-yearfrac.png\" alt=\"Calculate Age in Decimal Years - Example\" class=\"border imgshadow\"><\/p>\n<div class=\"caution-box\">\n<div class=\"caution-label\">Caution<\/div>\n<p>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 (&lt; 1% of date combinations I tested), this method is not a valid substitute for DATEDIF(start,end,\"y\").<\/p>\n<\/div>\n<h2 id=\"age-in-ymd\">Calculate Age in Years, Months and Days<\/h2>\n<p>Ages are often represented using the format <b>5y 11m 3d<\/b> or <b>5 years 11 months and 3 days<\/b>. 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.<\/p>\n<p>For example, if you subtract a month from 31-Mar-2017, should it be 28-Feb-2017? If so, we can use EDATE(<i>end_date<\/i>,-1) or EOMONTH(<i>end_date<\/i>,-1). However, if we use the DATE(<i>year<\/i>,<i>month<\/i>-1,<i>day<\/i>) method, the result would be 3-Mar-2017. If we assume a 30-day month, then (<i>end_date<\/i>-30) results in 1-Mar-2017. The point is that there may be multiple right answers, depending on the methodology.<\/p>\n<h3>Method 1: Use DATEDIF to Calculate Age in Years, Months &amp; Days<\/h3>\n<h4><b>Step 1<\/b>: Calculate the number of complete <b>years<\/b><\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">years<\/span> =<span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start_date<\/span>, <span class=\"arg\">end_date<\/span>, \"y\")<\/pre>\n<h4><b>Step 2<\/b>: Calculate the remaining number of complete <b>months<\/b> after subtracting years from the end date.<\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">months<\/span> =<span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start_date<\/span>, <span class=\"arg\">end_date<\/span>, \"ym\")<\/pre>\n<h4><b>Step 3<\/b>: Calculate the remaining number of <b>days<\/b> after subtracting months from the end date.<\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">days<\/span> =<span class=\"function\">DATE<\/span>( <span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>), <span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>) - <span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start<\/span>,<span class=\"arg\">end<\/span>,\"m\"), <span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)) - <span class=\"arg\">start<\/span><\/pre>\n<div class=\"caution-box\">\n<div class=\"caution-label\">Caution<\/div>\n<p>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 =<i>end<\/i>-DATE(YEAR(<i>end<\/i>),MONTH(<i>end<\/i>,1) listed on the Microsoft Support site <a href=\"https:\/\/support.office.com\/en-us\/article\/Calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">here<\/a> and <a href=\"https:\/\/support.office.com\/en-us\/article\/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">here<\/a> is not correct (e.g. 31-Jan-2016 to 31-Jan-2017 returns the value 30 and it should be 0).<\/p>\n<\/div>\n<h4><b>Step 4<\/b>: Concatenate the results from steps 1-3<\/h4>\n<p>The formula below returns a text string that looks like <b>49y 11m 6d<\/b>.<\/p>\n<pre class=\"XLformula\">=<span class=\"arg\">years<\/span> &amp; \"y \" &amp; <span class=\"arg\">months<\/span> &amp; \"m \" &amp; <span class=\"arg\">days<\/span> &amp; \"d\"<\/pre>\n<p>If you want a single formula that combines steps 1-4, you can combine the formulas to get the following mega formula.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start_date<\/span>, <span class=\"arg\">end_date<\/span>, \"y\") &amp; \"y \" &amp; <span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start_date<\/span>, <span class=\"arg\">end_date<\/span>, \"ym\")  &amp; \"m \" &amp; (<span class=\"function\">DATE<\/span>( <span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>), <span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>) - <span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start<\/span>,<span class=\"arg\">end<\/span>,\"m\"), <span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)) - <span class=\"arg\">start<\/span>) &amp; \"d\"<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>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).<\/p>\n<\/div>\n<h3>Method 2: The 30-Day Month Technique<\/h3>\n<p>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.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/calculate-age-using-30-day-method.png\" alt=\"Calculate Age Using the 30-Day Method\" class=\"border imgshadow\"><\/p>\n<h4><b>Step 1<\/b>: Subtract the <b>days<\/b> in the DD place, borrowing 30 days from the MM place if needed.<\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">days<\/span> =<span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)+30*(<span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)&lt;<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>))-<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>)<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>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.<\/p>\n<\/div>\n<h4><b>Step 2<\/b>: Subtract the <b>months<\/b> in the MM place, borrowing 12 months from the YY place if needed.<\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">months<\/span> =<span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>) - 1*(<span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)&lt;<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>)) + 12*((<span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>) - 1*(<span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)&lt;<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>)))&lt;<span class=\"function\">MONTH<\/span>(<span class=\"arg\">start<\/span>)) - <span class=\"function\">MONTH<\/span>(<span class=\"arg\">start<\/span>)<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>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.<\/p>\n<\/div>\n<h4><b>Step 3<\/b>: Subtract the <b>years<\/b> in the YYYY place.<\/h4>\n<pre class=\"XLformula\"><span class=\"arg\">years<\/span> =<span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>) - 1*( (<span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>)-1*(<span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)&lt;<span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>))) &lt; <span class=\"function\">MONTH<\/span>(<span class=\"arg\">start<\/span>) ) - <span class=\"function\">YEAR<\/span>(<span class=\"arg\">start<\/span>)<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>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.<\/p>\n<\/div>\n<p>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.<\/p>\n<p><!--\n\n\n<p>When using the 8870 technique to calculate the birthdate from the death date and age (after calculating the age using the 30-day month method), the birthdate is different from the original birthdate only about 2% of the time (when the original birthdate is the 31st of the month).<\/p>\n\n\n--><\/p>\n<h2 id=\"calculate-birthdate\">Calculate the Birthdate if you Know the Death Date and Age<\/h2>\n<p>Use DATE to <b>subtract<\/b> a combination of <i>years<\/i>, <i>months<\/i> and <i>days<\/i> from a date. Whether the answer is correct depends upon the methodology used to calculate the age.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">DATE<\/span>(<span class=\"function\">YEAR<\/span>(<span class=\"arg\">end_date<\/span>)-<span class=\"arg\">years<\/span>, <span class=\"function\">MONTH<\/span>(<span class=\"arg\">end_date<\/span>)-<span class=\"arg\">months<\/span>, <span class=\"function\">DAY<\/span>(<span class=\"arg\">end_date<\/span>)-<span class=\"arg\">days<\/span>) <\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>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.<\/p>\n<\/div>\n<h2 id=\"calculate-deathdate\">Calculate the Death Date if you Know the Birth Date and Age<\/h2>\n<p>Use DATE to <b>add<\/b> a combination of <i>years<\/i>, <i>months<\/i> and <i>days<\/i> to a date. Whether the answer is correct depends upon the methodology used to calculate the age.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">DATE<\/span>(<span class=\"function\">YEAR<\/span>(<span class=\"arg\">start_date<\/span>)+<span class=\"arg\">years<\/span>, <span class=\"function\">MONTH<\/span>(<span class=\"arg\">start_date<\/span>)+<span class=\"arg\">months<\/span>, <span class=\"function\">DAY<\/span>(<span class=\"arg\">start_date<\/span>)+<span class=\"arg\">days<\/span>)<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTES<\/div>\n<p>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).<\/p>\n<p>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.<\/p>\n<p><!--\n\n<p>If you use =DATEDIF(EDATE(<i>start<\/i>,DATEDIF(<i>start<\/i>,<i>end<\/i>,\"m\")),<i>end<\/i>,\"d\") for Step #3 to calculate remaining days, then you can use =EDATE(<i>start<\/i>,<i>years<\/i>*12+<i>months<\/i>)+<i>days<\/i> to return the original end date.<\/p>\n\n-->\n<\/div>\n<h2 id=\"pre-1900\">Calculate Age in Excel for Dates Prior to 1900<\/h2>\n<p>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 <b>store the date as text<\/b>. Below are a couple of methods for calculating age in Excel when working with dates prior to 1900.<\/p>\n<h3>Method 1: Add 2000 years then use the formulas above<\/h3>\n<p>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.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">DATEDIF<\/span>(\"2-Jan-3803\",\"28-Aug-3850\",\"y\")<\/pre>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>Adding 2000 years allows the formula to handle leap years correctly. Plus, it's easy to add and subtract 2000 years in your head.<\/p>\n<\/div>\n<h3>Method 2: Create a User-Defined Function based on VBA date functions<\/h3>\n<p>The VBA date functions can handle all valid Gregorian dates. See the article \"<a href=\"\/blog\/excel-formulas\/custom-user-defined-functions.html\">How to Create Custom Excel Functions<\/a>\" for the VBA code that can calculate ages for dates earlier than 1900.<\/p>\n<h3>Method 3: Calculate age using the 30-Day Month Method and YYYYMMDD date values<\/h3>\n<p>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 <b>0\"y \"00\"m \"00\"d\"<\/b> to display the age.<\/p>\n<pre class=\"XLformula\">=<span class=\"arg\">end<\/span> - <span class=\"arg\">start<\/span> - <span class=\"function\">IF<\/span>(<span class=\"function\">VALUE<\/span>(<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">end<\/span>-<span class=\"arg\">start<\/span>,2))>31,70) - <span class=\"function\">IF<\/span>(<span class=\"function\">VALUE<\/span>(<span class=\"function\">RIGHT<\/span>(<span class=\"function\">INT<\/span>((<span class=\"arg\">end<\/span>-<span class=\"arg\">start<\/span>)\/100),2))>12,8800)<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/calculate-age-using-8870-technique.png\" alt=\"Calculate Age Using the 8870 Technique\" class=\"border imgshadow\"><\/p>\n<div class=\"note-box\">\n<div class=\"note-label\">NOTE<\/div>\n<p>To convert a date value (>1900) to a YYYYMMDD value, use =VALUE(YEAR(<i>date<\/i>) &amp; TEXT(MONTH(<i>date<\/i>),\"00\") &amp; TEXT(DAY(<i>date<\/i>),\"00\"))<\/p>\n<p>You can use the following custom number format for YYMMDD age values to only display the years and months if they are not zero: <b>[>9999]0\"y \"00\"m \"00\"d\";[>99]0\"m \"00\"d\";0\"d\"<\/b><\/p>\n<p>For an age stored as a YYMMDD value, use <br \/>=VALUE(RIGHT(<i>yymmdd<\/i>,2)) to return the days, use <br \/>=VALUE(RIGHT(INT(<i>yymmdd<\/i>\/100),2)) to return the months and use <br \/>=INT(<i>yymmdd<\/i>\/10000) to return the years.<\/p>\n<\/div>\n<h2 id=\"datedif-md-bug\">DATEDIF \"md\" Bug and Work-Around<\/h2>\n<p>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.\"<\/p>\n<h3>Method 1: Return remaining days after <b>Adding<\/b> whole months to the <b>Start Date<\/b><\/h3>\n<p>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.<\/p>\n<pre class=\"XLformula\">=<span class=\"arg\">end<\/span> - <span class=\"function\">DATE<\/span>( <span class=\"function\">YEAR<\/span>(<span class=\"arg\">start<\/span>), <span class=\"function\">MONTH<\/span>(<span class=\"arg\">start<\/span>) + <span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start<\/span>,<span class=\"arg\">end<\/span>,\"m\"), <span class=\"function\">DAY<\/span>(<span class=\"arg\">start<\/span>))<\/pre>\n<p>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.<\/p>\n<h3>Method 2: Return remaining days after <b>Subtracting<\/b> whole months from the <b>End Date<\/b><\/h3>\n<p>The following formula is the correct work-around to use instead of the \"md\" option.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\">DATE<\/span>( <span class=\"function\">YEAR<\/span>(<span class=\"arg\">end<\/span>), <span class=\"function\">MONTH<\/span>(<span class=\"arg\">end<\/span>) - <span class=\"function\">DATEDIF<\/span>(<span class=\"arg\">start<\/span>,<span class=\"arg\">end<\/span>,\"m\"), <span class=\"function\">DAY<\/span>(<span class=\"arg\">end<\/span>)) - <span class=\"arg\">start<\/span><\/pre>\n<h2>Final Note<\/h2>\n<p>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.<\/p>\n<p>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.<\/p>\n<h2>References<\/h2>\n<div class=\"refblock\">\n<ul>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c\">DATEDIF Function<\/a> <i>support.office.com<\/i> - 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.<\/li>\n<li><a href=\"https:\/\/exceljet.net\/formula\/get-age-from-birthday\">Get Age From Birthday<\/a> <i>exceljet.net<\/i> - Shows the YEARFRAC method.<\/li>\n<li><a href=\"http:\/\/www.cpearson.com\/excel\/datedif.aspx\">The DATEDIF Worksheet Function<\/a> <i>cpearson.com<\/i> - 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.<\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to Calculate Age in Excel using DATEDIF and other Excel formulas. Calculate age in years or a combination of years, months and days. Learn an accurate work-around for the &#8220;md&#8221; option.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3404","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3404"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3404\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}