< Go Back

Get months between dates

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

=DATEIF(C7,D7,”M")

Example

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.

Syntax

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 UnitThe 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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar