Difference in months between two dates

To calculate the difference in months between two dates you can apply the Excel DATEDIF function

Example: Difference in months between two dates

Difference in months between two dates

METHOD 1. Difference in months between two dates

EXCEL

=DATEDIF(B5,C5,D5)
The formula returns the number of months between the two selected dates. The start and end dates are captured in cells B5 and C5, respectively. Cell D5 captures the time unit, which in this case letter m represents months. If you prefer to enter formula arguments manually, you will need to use double quotation marks (e.g. =DATEDIF("15/03/2017","20/10/2017","m")).

METHOD 1. Difference in months between two dates using VBA

VBA

Sub Difference_in_months_between_two_dates()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
Set date1 = ws.Range("B5")
Set date2 = ws.Range("C5")
Set smonth = ws.Range("D5")
'calculate the difference in months between two dates
ws.Range("E5") = DateDiff(smonth, date1, date2)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Start Date: If using the exact VBA code the start date needs to be captured in cell ("B5").
End Date: If using the exact VBA code the end date needs to be captured in cell ("C5").
Time Unit: If using the exact VBA code the time unit needs to be captured in cell ("D5"), which will hold letter m.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Start Date: Select the cell that captures the start date by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
End Date: Select the cell that captures the end date by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Time Unit: Select the cell that captures the time unit by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

METHOD 2. Difference in months between two dates using VBA with a formula function

VBA

Sub Difference_in_months_between_two_dates()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate the difference in months between two dates
ws.Range("E5").Formula = "=DATEDIF(B5,C5,D5)"

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Start Date: If using the exact VBA code the start date needs to be captured in cell ("B5").
End Date: If using the exact VBA code the end date needs to be captured in cell ("C5").
Time Unit: If using the exact VBA code the time unit needs to be captured in cell ("D5"), which will hold letter m.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Start Date: Select the cell that captures the start date by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
End Date: Select the cell that captures the end date by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Time Unit: Select the cell that captures the time unit by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

Explanation about the formula used to calculate the difference in months between two dates

EXPLANATION

EXPLANATION
To calculate the difference in months between two dates you can apply both and Excel and VBA methods. Using an Excel method you can apply the Excel DATEDIF function. If using VBA, we either use a DateDiff function or apply the DATEDIF formula.
FORMULAS
=DATEDIF(start_date,end_date,time_unit)

ARGUMENTS
start_date: The starting date from which you want to begin counting months.
end_date: The end date up to which to count the number of months.
time_unit: The time unit representation. In this tutorial given we are calculating the number of months between two dates we would apply the letter m.