Get Months Between Two Dates
To calculate months between two dates as a whole number, you can use the DATEDIF function. In the example shown, the formula in E7 is
Figure1. Example of How to get months between dates
What Is DATEDIF Function?
Calculates the number of months, days, or years between two dates as whole numbers. Excel provides the DATEDIF function in order to support older workbooks from Lotus 1>2>3. This one is very easy to understand. The DATEDIF function may calculate incorrect results under some methods and values.
DATEDIF is one of the very few undocumented functions in Excel, and because it is “hidden” you won’t find it on the Formula tab, nor will you get any hint on which arguments to enter when you start typing the function’s name in the formula bar.
Its syntax is very easy. All three arguments are required:
Start_date – the initial date of the period you want to calculate.
End_date – the ending date of the period.
“M” a Unit – The number of complete months in the period.
In this example, the start date is in cell C7, and the end date is in D7. In the formula, the “m” returns the number of full months between the two days.
How DATEDIF Works:
DATEDIF takes 3 arguments: start_date, end_date, and unit. It subtracts start date from end date and calculates Number of the month. In this case, we want months, so we supply “m” for the unit.
Excel has several built-in dates that can be used to calculate the number of months between two dates. Each date function does a different job so that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.
DATEDIF Function Error Values:
If the data for the various arguments of this function are not entered correctly the following error values appear in the cell where the DATEDIF function is located:
-> #VALUE! error – returned if either start_date or end_date are not actual dates.
->#NUM! error – returned if end_date is an earlier date than start_date.