Calculating the Number of Years and Months between Dates in Excel - dummies

Calculating the Number of Years and Months between Dates in Excel

Sometimes you need to be able to determine the amount of time between dates in years and months. For example, you can determine that the amount of time elapsed between November 23, 1960, and May 13, 2014, is 53 years and five months.

The formula that you use to find out the time between two dates in years and months uses a text string with two DATEDIF functions.

Cell C4 shown contains the following formula:

image0.jpg

=DATEDIF(A4,B4,"Y") & " Years, " & DATEDIF(A4,B4,"YM") & " Months"

You accomplish this task by using two DATEDIF functions joined in a text string with the ampersand (&) operator.

The first DATEDIF function calculates the number of years between the start and end dates by passing the year time unit (“Y”):

DATEDIF(A4,B4,"Y")

The second DATEDIF function uses the “YM” time unit to calculate the number of months, ignoring the year portion of the date:

DATEDIF(A4,B4,"YM")

Finally, you join these two functions with some text of your own to let users know which number represents years and which represents months:

=DATEDIF(A4,B4,"Y") & " Years, " & DATEDIF(A4,B4,"YM") & " Months"