Calculate Years and Months between Two Dates in Excel (6 Approaches)

All of our everyday tasks depend on time. We must keep track of time because time is a transient circumstance. Years, months, days, etc. are used to measure the time. In Microsoft Excel, we can calculate the years, months, days, hours, etc.; actually the measurements of time between two dates. In this article, we will demonstrate different ways to calculate years and months between two dates in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


6 Different Approaches to Calculate Years and Months between Two Dates

Suppose, you are in charge of a number of tasks, you may want to remember how several months and years have transpired between two dates. Or you need to calculate someone’s age between two dates.

To calculate years and months between two dates we are going to use this dataset. The dataset contains some start dates and end dates and the number of years and months passed between the two dates. Now, Let’s go through the methods down and learn to calculate years and months using those methods.

6 Different Approaches to Calculate Years and Months between Two Dates


1. DATEDIF Function to Calculate Years and Months between Two Dates

To calculate years and months between two dates, the DATEDIF function is the best option in Excel. Let’s have a basic understanding of the DATEDIF function first.

Syntax:

The syntax for the DATEDIF function is:

DATEDIF(start_date,end_date,unit)

Arguments:

start_date[required] The first date is often known as the commencement date.

end_date[required] A date that signifies the end of a period of time.

unit: The kind of information you’d like to get (“y”, “m”, “ym”, etc)


1.1. Calculate Age in Years

Assume that you want to calculate the age of some people for a particular time. For this, we need to know the birth date which is the start date in our dataset and also we need to know the exact day which we consider as the end date in our dataset. Now let’s calculate how old they were on that particular date. To calculate the year, we are using the DATEDIF function. Let’s follow the steps down.

STEPS:

  • Firstly, select the cell where you want the calculated year. So we select cell D5.
  • Secondly, type the formula on that cell.
=DATEDIF(B5,C5,"y")

Here, cell B5 indicates the start date and cell C5 indicates the end date. And we use “y” as the unit because we want the difference between two dates in years.

  • After that, press Enter. And the formula will show in the formula bar.

6 Different Approaches to Calculate Years and Months between Two Dates

  • Now, to copy the formula over the dataset, drag the Fill Handle down.

6 Different Approaches to Calculate Years and Months between Two Dates

  • And, that’s it! The years are calculated in the resulting cell.


1.2. Calculate Age in Months

Likewise the previous section of 1.1, now we want to calculate the months of those persons. Again, we are using the DATEDIF function to calculate the months. For this, we need to go after the procedures.

STEPS:

  • By the same token, choose the cell where you wish the calculated month to appear. As a result, we choose cell D5.
  • Second, in that cell, type the formula.
=DATEDIF(B5,C5,"m")

Here, cell B5 represents the start date, whereas cell C5 represents the end date. We choose “m” as the unit because we’re looking for the difference in months between the two dates.

  • After that, hit the Enter key. In the formula bar, the formula will appear.

6 Different Approaches to Calculate Years and Months between Two Dates

  • Next, drag the Fill Handle down to duplicate the formula over the dataset.

6 Different Approaches to Calculate Years and Months between Two Dates

  • That’s all there is to it! In the resultant cell, the months are calculated.


1.3. Marge Formula to Calculate Age in Years and Months

Here, we want to calculate the years and months together using the DATEDIF function. Let’s see the steps below.

STEPS:

  • As same as the previous methods, select the cell where you want the calculated years and months to appear. As a result, we choose cell D5.
  • Second, enter the formula into that cell.
=DATEDIF(B5,C5,"y")&" years" &", "&DATEDIF(B5,C5,"m")&" months"

Here, cell B5 signifies the start date, and cell C5 signifies the end date.

  • Then press the Enter key. And the formula will be displayed in the formula bar.

  • Further, drag the Fill Handle down to copy the formula over the dataset.

6 Different Approaches to Calculate Years and Months between Two Dates

  • That’s it! In the resulting cell, the years and months are computed.

Read More: How to Use DateDiff Function in Excel VBA (5 Examples)


2. Calculate Years and Months Using Excel LET Function

By using the LET function, we can calculate the years and months between two dates in Excel. Let’s start with a fundamental overview of the LET function.

Syntax:

The syntax for the LET function is:

LET (name1, name_value1, [name2], [name_value2], …, calculation)

Arguments:

name1: [required] This is the first name to be assigned. It needs to start with a letter.

name_value1: [required] Name1’s assigned value or calculation.

[name2] / [name_value2]: [optional] The second name, as well as its worth.

calculation: [optional] A calculation that employs names and values that have been assigned.

Now, Let’s have a look at the steps below.

STEPS:

  • In the beginning, select the cell where you want the calculated years and months to appear. So we pick cell D5.
  • Furthermore, insert the formula in that cell.
=LET(start,B5,end,C5, DATEDIF(start,end,"y")&" years, "& DATEDIF(start,end,"m")&" months")

Calculate Years and Months Using Excel LET Function

Here, cell B5 denotes the start date, while cell C5 denotes the end date.

  • Then, press the Enter key. And the formula will appear in the formula bar.

Calculate Years and Months Using Excel LET Function

  • Now, drag the Fill Handle down to copy the formula over the range.

Calculate Years and Months Using Excel LET Function

  • And finally, the years and the months are calculated in the resulting cells.

Calculate Years and Months Using Excel LET Function

Read More: How to Calculate Years Between Two Dates in Excel (2 Methods)


3. YEARFRAC Function to Calculate Years & Months between Two Dates

We can calculate years and months with the YEARFRAC function in Excel. First, let’s review the YEARFRAC function in its most basic form.

Syntax:

The syntax for the YEARFRAC function is:

YEARFRAC(start_date, end_date, [basis])

Arguments:

start_date: [required] The start date is represented by this date.

end_date: [required] The termination date is represented by this date.

[basis]:[optional] The day count basis to be used.


4.1. Calculate Years between Two Dates

To calculate years using the YEARFRAC function we need to follow the steps below.

STEPS:

  • To begin, choose the cell in which you want the calculated years to appear. So, we choose D5.
  • Then, write down the formula there.
=YEARFRAC(B5, C5)

Here, cell B5 symbolizes the starting date, and cell C5 symbolizes the ending date.

  • After that, press Enter. The formula will be shown in the formula bar.

  • Further, drag the Fill Handle down to repeat the formula over the set of data.

  • And, in the end, you will see the result by following the above steps.


4.2. Calculate Months between Two Dates

To use the YEARFRAC function to calculate months, follow the instructions below.

STEPS:

  • To begin, choose the cell wherein you want to get the calculated months. So, we choose cell D5.
  • Second, type down the formula in that cell.
=YEARFRAC(B5,C5)*12

Here, cell B5 indicates the start date, and cell C5 indicates the end date.

  • Then, hit Enter. And you will be able to see the formula in the formula bar.

  • After that, copy the formula over the range D5:D8 by dragging the Fill Handle down.

Calculate Months between two Dates

  • Finally, the months are calculated between the two dates.

Read More: How to Calculate Years in Excel from Today (4 Ways)


Similar Readings:


4. Calculate the Duration between Two Dates Without Zero Value

Suppose, we need to find the difference between two dates without displaying the zero values. We can do this by combining the IF and DATEDIF functions in Excel. Let’s take a look at the steps below.

STEPS:

  • Firstly, select cell D5.
  • Secondly, type the formula there.
=IF(DATEDIF(B5,C5,"y"), DATEDIF(B5,C5,"y")&" years","")& IF(DATEDIF(B5,C5,"m"),", "&DATEDIF(B5,C5,"m")&" months","")

Calculate the Duration between Two Dates Without Zero Value

  • After that, drag the Fill Handle down.

Calculate the Duration between Two Dates Without Zero Value

  • Finally, this will calculate the duration between two dates without having zero values.

Calculate the Duration between Two Dates Without Zero Value

Read More: How to Calculate Working Days between Two Dates in Excel (4 Methods)


5. Combine YEAR and MONTH Functions to Count Months in Excel

To calculate months between two dates we can use the YEAR and MONTH functions together in Excel. Let’s take a look at the procedures below.

STEPS:

  • In the first place, select cell D5.
  • Then, write the formula in that cell.
=(YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5)

Combine YEAR and MONTH Functions to Count Months in Excel

  • After that, hit Enter. And, the formula will appear in the formula box.

Calculate the Duration between Two Dates Without Zero Value

  • Next, simply drag the Fill Handle down.

Combine YEAR and MONTH Functions to Count Months in Excelates Without Zero Value

  • And, there you go!

Read More: How to Count Months in Excel(5 ways)


6. Excel VBA to Calculate Years and Months Between Two Dates

With Excel VBA, we may do a wide range of activities. We can calculate years and months between two dates using the VBA Code. So let’s see the steps down to use the VBA to calculate years and months between two dates.

STEPS:

  • First, go to the Developer tab on the ribbon.
  • Second, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

Excel VBA to Calculate Years and Months Between Two Dates

  • Another way to open the Visual Basic Editor is, simply right-click on the sheet and select View Code.

  • This will open the Visual Basic Editor where you can write the code.
  • Now, write down the VBA Code below.

VBA Code:

Sub Years_and_Months_Between_Two_Dates()
    Dim StartDate As Date, EndDate As Date
    Dim year As Integer
    Dim month As Integer
    StartDate = "1-Jan-2020"
    EndDate = "15-Mar-2022"
    year = DateDiff("yyyy", StartDate, EndDate)
    month = DateDiff("m", StartDate, EndDate)
    MsgBox "Number of years: " & year, vbInformation, "Years Between Two Dates"
    MsgBox "Number of months: " & month, vbInformation, "Months Between Two Dates"
End Sub
  • Next, run the code by pressing the F5 key or clicking the Run Sub button.

  • And, this will first show the number of years in the MsgBox.

  • Then, clicking on the OK button will next show the number of months between the given two dates.

Related Content: How to Add/Subtract Years to a Date in Excel


Conclusion

The above methods will assist you to calculate years and months between two dates in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo