Get months between dates

Get months between dates

Generic formula 

=DATEDIF(start_date,end_date,"m")

Related formulas 

Get days between dates

Get workdays between dates

Explanation

To calculate months between two dates as a whole number, you can use the DATEDIF function.

In the example shown, the formula in D6 is:

=DATEDIF(B6,C6,"m")

Note that the DATEDIF automatically rounds down. To round up to the nearest month, see below.

How this formula works

The DATEDIF function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it's only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

DATEDIF takes 3 arguments: start date, end_date, and unit. In this case, we want months, so we supply "m" for unit.

DATEDIF automatically calculates and returns a number for months, rounded down.

Nearest whole month

DATEDIF rounds down by default. If you want to calculate months to the nearest whole month, you can make a simple adjustment to the formula:

=DATEDIF(start_date,end_date+15,"m")

This ensures that end dates occurring in the 2nd half of the month are treated like dates in the following month, effectively rounding up the final result.

Get months between dates

Generic formula 

=DATEDIF(start_date,end_date,"m")

Related formulas 

Get days between dates

Get workdays between dates

Explanation

To calculate months between two dates as a whole number, you can use the DATEDIF function.

In the example shown, the formula in D6 is:

=DATEDIF(B6,C6,"m")

Note that the DATEDIF automatically rounds down. To round up to the nearest month, see below.

How this formula works

The DATEDIF function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it's only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

DATEDIF takes 3 arguments: start date, end_date, and unit. In this case, we want months, so we supply "m" for unit.

DATEDIF automatically calculates and returns a number for months, rounded down.

Nearest whole month

DATEDIF rounds down by default. If you want to calculate months to the nearest whole month, you can make a simple adjustment to the formula:

=DATEDIF(start_date,end_date+15,"m")

This ensures that end dates occurring in the 2nd half of the month are treated like dates in the following month, effectively rounding up the final result.