Using Excel to figure out the number of months between two dates (Easy Formulas)

You would need to know how many months had gone between two dates if you were in charge of several projects. Alternatively, if you’re planning a project, you might need to know the start and finish dates as well.

The number of months between two dates can be determined in a variety of ways (all using different formulas).

I’ll teach you several formulae in this article that you may use to calculate the number of months between two dates.

then let’s get going!

This instruction explains:
1. Syntax for Excel VBA Autofilter
1.1 As an illustration, filter data based on text criteria.
1.2 An illustration would be several criteria (AND/OR) in the same column.
1.3 Example: Several criteria in several columns
1.4 Use the AutoFilter Method to Filter the Top 10 Records, as an example.
1.5 Use the AutoFilter Method to Filter the Top 10%, for Example.
1.6 Using a wildcard character in an auto filter, for instance.
1.7 An illustration is Copy Filtered Rows into a New Sheet.
1.8 Example: Use a cell value to filter data
2. Using VBA, turn Excel AutoFilter ON or OFF
3. Verify whether an auto filter has already been used
4. Display All Data
5. Making Use of AutoFilter with Protected Sheets

1. Syntax for Excel VBA Autofilter(Find the total number of months between the two dates):

You are unlikely to obtain dates within the ideal amount of months. The two dates are more likely to encompass a certain number of months and a certain number of days.

For instance, there are 2 months and 15 days between January 1 and March 15, 2020.

The DATEDIF function can be used to get the total number of months that separate two dates.

Imagine you simply want to know the total number of months for a dataset like the one below (and not the days).

The DATEDIF formula that does it is shown below:

=DATEDIF(A2,B2,"M")

Only the total number of completed months between two dates will be provided by the aforementioned algorithm.

One of the few undocumented Excel functions is DATEDIF. When you type the =DATEDIF in a cell in Excel, you would not see any IntelliSense or any guidance on what arguments it can take. Therefore, you must be familiar with the syntax to use DATEDIF in Excel.

Use the following formula to calculate the total number of months and days between two dates:

=DATEDIF(A2,B2,"M")&"M "&DATEDIF(A2,B2,"MD")&"D"

Note:

When calculating the month numbers, the DATEDIF function will not include the start date. The DATEDIF function, for instance, will calculate the number of months as 0 if a project begins on January 1 and ends on January 31 because it ignores the start date and calculates that only 30 days of January have passed.

Utilizing YEARFRAC (Get Total Months Between Two Dates)

The YEARFRAC function can be used as an alternative technique to determine how many months there are between two supplied dates.

The YEARFRAC function will accept two dates as input parameters and will return the number of years that have elapsed between the two dates.

When a year has not passed between two dates, the YEARFRAC function, unlike the DATEDIF function, will provide you with the values in decimal.

For instance, if my start date is January 1, 2020, and my end date is January 31, 2020, the YEARFRAC function’s output will be 0.833. Once you get the year value, multiply it by 12 to obtain the month value.

Let’s say you want to know how many months there are between the start and finish dates for the dataset below.

The equation to accomplish this is given below:

=YEARFRAC(A2,B2)*12

You will receive the months in decimals as a result.

If you simply need to know the total number of months, you may wrap the calculation above in INT (as seen below):

=INT(YEARFRAC(A2,B2)*12)

The YEARFRAC function will view the start date as a component of the month, which is another significant distinction between the DATEDIF function and the YEARFRAC function. For instance, if the start date is January 1 and the end date is January 31, the outcome of the calculation above would be 1.

The outcomes of DATEDIF and YEARFRAC are contrasted below.

Implementing the YEAR/MONTH Formula (Count All Months when the Project was Active)

This approach may be used to determine the total number of months that are covered between the start and finish dates.

Assume you possess the dataset depicted below:

The calculation that will show you how many months there are between the two dates is shown below:

=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

The YEAR function, which utilizes the date to provide the year number, and the MONTH function are both used in this calculation (which gives you the month number using the date).

The month of the commencement date is likewise totally disregarded in the method above.

For instance, the method below will return 1 if your project begins on January 1 and finishes on February 20 since it entirely disregards the start date month.

Use the following formula if you’d want it to also take the month of the commencement date into account:

=(YEAR(B2)-YEAR(A2))*12+(MONTH(B2)-MONTH(A2)+1)

If you wish to calculate how many months this project was active, you might want to apply the formula above (which means that it could count the month even if the project was active for only 2 days in the month).

In Excel, these three methods demonstrate how to determine the number of months between two dates. The approach you use will depend on the calculations you plan to make (a brief explanation is provided below):

• If you wish to determine the total number of completed months between two dates, use the DATEDIF function method (it ignores the start date)
• When you wish to get the precise number of months that have passed between two dates, use the YEARFRAC technique. Additionally, decimal results are provided (where the integer value represents the number of full months and the decimal part represents the number of days)
• When calculating the number of months between two dates, use the YEAR and MONTH technique (even when the duration between the start and the end date is only a few days)

Each of the formulas discussed in this lesson will compute the number of months between two dates as shown below:

I hope this Excel tutorial was helpful.