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.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, I want a formula to calculate the number months between dates and display text based on criteria for example. If 3 months critical if 6 months standard if less than 1 month critical
Solved by E. A. in 14 mins
group the dates into months
Solved by G. W. in 17 mins
I am trying to use datedif to calculate the number of months between two dates, and I keep on getting the result on a weird format instead of just a number
Solved by A. A. in 19 mins

Leave a Comment

avatar