Calculating Dates with Years and Months

Hi. Good day everybody...

Some people may wonder how to calculate the date difference between two date values in Excel. It may be easy if you just want to show the difference in floating number (number with decimal). For example, if the first date is a birth date stored in cell B1 and you want to show the difference in cell B2 as number of years, you can simply write the expression in cell B2 as the following:

=(TODAY()-B1)/365

On the other hand, if you want to show the answer as number of years and months, it's not that simple. The expression can be as long as the following (or may be even longer):

=IF(MONTH(TODAY())>=MONTH(B1),

(YEAR(TODAY())-YEAR(B1))&" years "&(MONTH(TODAY())-MONTH(B1))&" months ",

(YEAR(TODAY())-YEAR(B1)-1)&" years "&(12+MONTH(TODAY())-MONTH(B1))&" months ")

Anyway, what the above expression means ...

IF the MONTH in today's date is greater than or equal to the MONTH in cell B1, calculate number of years using the YEAR in today's date minus the YEAR in cell B1 and calculate number of months using the MONTH in today's date minus the MONTH in cell B1. Otherwise, if the MONTH in today's date is less than the MONTH is cell B1, calculate number of years using the YEAR in today's date minus the YEAR in cell B1 minus 1 and calculate number of months using the MONTH in today's date minus the MONTH in cell B1 plus 12. In other words, if the person's birth date not yet reach its anniversary, we have to minus 1 to the difference of years and add 12 to the difference of months.