Calculate your age, WITHOUT using Excel’s DateDif function.

          My previous post on using Excel’s DATEDIF function resulted in rather productive discussions on LinkedIn. First of all, some users are concerned that the DATEDIF function will no longer be supported in Excel and will cease to exist at some point in the future. Microsoft openly states that “this function is provided for compatibility with Lotus 1-2-3.” , and given the history of this support, I don’t see why it will be discontinued. Nonetheless, I will make an attempt to replace DATEDIF function with other functions readily available in the program. Secondly, as a reminder that English is not the only language in the world, Microsoft created “local” versions of this function. A user confirmed existence of the SIFECHA function, but the syntax to use it, involves using semicolons, instead of commas. If you are using Spanish version of Excel, you might want to try both versions:

SIFECHA(fecha_inicial,fecha_final,unidad)
SIFECHA(fecha_inicial;fecha_final;unidad)

          According to this site , remaining versions of this function include:

          In this post I will use two different methods of calculating date differences. The first method involves simple math calculations, and number formatting options (INT, ROUNDUP), the second uses Excel’s DATE functions: DATE, YEAR, MONTH, and DAY. As a disclaimer, while I performed some rudimentary quality assurance, I am not certain that either of these methods is as accurate as the DATEDIF function for figuring out your age in years, months, and days. In fact, depending on the dates involved, one or another will be more accurate. As an example, the actual birth date in a different year, will be inaccurate in some instances of using the math approach. While certain rounding errors will inadvertently affect calculations in both methods, calculations in Years ONLY, Months ONLY, or Days ONLY seem to be accurate.

          Method 1 : Using simple math.
          We know that for any given consecutive 4 years, three of them will have 365 days, while one (leap year) will be 366 days long. As a result the average number of days in a year is 365.25, (1,461/4.) The next step is to calculate number of days in a month. The answer is 30.4375 (365.25/12.) Since we are interested in complete years/months/days, we would like to round DOWN our result to the nearest integer. There is an Excel function for that, it’s called: INT

          Armed with this knowledge, let’s perform our simple calculations, assuming that cell B1 still has the first Windows release of Microsoft Office on 6-October-1987 , while cell B2 holds today’s date:

          To calculate number of days passed, we can simply subtract earlier date from the later date, and wrapping the result within the INT function, we will have:

= INT(B2-B1)  & " days old!"

          To calculate number of months passed, we can simply subtract earlier date from the later date, and divide the result by 30.4375 (number of days in a month):

= INT((B2-B1)/30.4375) & " months old!"

          To calculate number of years passed, we can simply subtract earlier date from the later date, and divide the result by 365.25 (number of days in a year):

= INT((B2-B1)/365.25) & " years old!"

          The next step is to calculate age in years, months, and days. To simplify this task, let’s perform these calculations separately, and then combine (concatenate) our results:

          Formula for number of years stays the same, but we need to suppress printing results when number of years is 0:

= IF(INT((B2-B1)/365.25)=0,"",INT((B2-B1)/365.25) & " year(s), ") 

          To calculate number of months, let’s introduce MOD function first. This function calculates the remainder “after a number is divided by a divisor.” In other words, after figuring out number of complete years, we need to divide the remainder by 30.4375. Let’s not forget to suppress printing the result, when number of months is 0:

= IF(INT(MOD(B2-B1,365.25)/30.4375) = 0, "", INT(MOD(B2-B1,365.25)/30.4375) & " month(s), and ") 

          Finally, to calculate number of days, we need the remainder of divisions by 365.25 AND then 30.4375. During my testing, I decided to also use ROUNDUP function, which is the opposite of INT, as well as the ABS function (absolute value):

= ROUNDUP(ABS((MOD(MOD(B2-B1,365.25),30.4375))),0) & " day(s) old!"

          The resulting formula now becomes:

= IF(INT((B2-B1)/365.25)=0,"",INT((B2-B1)/365.25)&" year(s), ") & IF(INT(MOD(B2-B1,365.25)/30.4375) = 0, "", INT(MOD(B2-B1,365.25)/30.4375) & " month(s), and ") & ROUNDUP(ABS((MOD(MOD(B2-B1,365.25),30.4375))),0) & " day(s) old!"

          Method 2 : Using Excel’s date functions (DATE, YEAR, MONTH, and DAY.)
          Formula for calculating number of days stays the same as in the previous method.

          Before calculating number of months passed, let’s accommodate different month dates and subtract 1, as needed. Function DAY returns day of the month. Function YEAR returns year of the date, and function MONTH yields month of the date. The formula requires us to calculate difference between years involved, calculate the result by 12 (12 months in a year,) and then add a difference between months involved:

IF(DAY(B2)>=DAY(B1),0,-1)+(YEAR(B2)-YEAR(B1))*12+MONTH(B2)-MONTH(B1) & " months old!"

          To calculate number of years, let’s introduce the final function for this post: DATE, which returns numeric equivalent of any date passed. We need to subtract 1 in certain scenarios:

= IF( DATE(,MONTH(B1),DAY(B1)) <= DATE(,MONTH(B2),DAY(B2)),INT(YEAR(B2) - YEAR(B1)) & " years old", INT(YEAR(B2) - YEAR(B1) - 1) & " years old")

          It would probably be easier for you to download my sample file , but in case you are interested in seeing the final formula for this method for calculating one’s age in years, months, and days, here it is:

=IF(IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),INT(YEAR(B2)-YEAR(B1)),INT(YEAR(B2)-YEAR(B1)-1)) = 0,"",IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),INT(YEAR(B2)-YEAR(B1)),INT(YEAR(B2)-YEAR(B1)-1))  & " year(s), ") & IF(IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),( YEAR(B2) - YEAR(B2) )*12+MONTH(B2)-MONTH(B1),(YEAR(B2)-(YEAR(B2)-1))*12+MONTH(B2)-MONTH(B1))=0,"",IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),( YEAR(B2) - YEAR(B2) )*12+MONTH(B2)-MONTH(B1),(YEAR(B2)-(YEAR(B2)-1))*12+MONTH(B2)-MONTH(B1)) & " month(s), and ") &  INT(ABS((INT(B2-B1)-(IF(IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),INT(YEAR(B2)-YEAR(B1)),INT(YEAR(B2)-YEAR(B1)-1))=0,"",IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),INT(YEAR(B2)-YEAR(B1)),INT(YEAR(B2)-YEAR(B1)-1))*365.25)) - (IF(DATE(,MONTH(B1),DAY(B1))<=DATE(,MONTH(B2),DAY(B2)),( YEAR(B2) - YEAR(B2) )*12+MONTH(B2)-MONTH(B1),(YEAR(B2)-(YEAR(B2)-1))*12+MONTH(B2)-MONTH(B1)) * 30.4375)))) & " day(s) old!"