**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!"

Hi,

thanks for mentioning my site for the Excel-Translator. PS: the site is also available in English at its subdomain http://en.excel-translator.de ðŸ™‚

Best Regards,

Mourad

Thanks for stopping by, Mourad! You’ve created a really useful resource that will make a life of Excel users much easier. I’ve updated your link to the English language version.

Semicolon is needed, instead of commas, ONLY when your number “locale” uses comma as decimal separator. This is true for spanish in spain (not MÃ©xico and most other spanish locales).

Try semicolons in english, they work nicely. Ancient 1-2-3 used semicolons, not commas.

Thank you for your comment, Javier! This explains why a reader from Spain couldn’t get SIFECHA function to work with commas.

Using my US version Excel program, I tried using semicolons to no avail in DATEDIF function, as per Microsoft’s knowledge base, commas still work though.

I have noticed you don’t monetize your blog, don’t waste your

traffic, you can earn extra cash every month because you’ve got high quality content.

If you want to know how to make extra bucks, search for:

Boorfe’s tips best adsense alternative