Formula time.  Today let’s look at how to calculate how many months there are between two dates. Why?. It’s a  question I was reacently asked.  This is useful if you have a list of dates say one column with Start Dates and another column containing End Dates, this could be a contract start and end date or first and last invoice date.

First things first.  Our data is set up as below. We have contract Start Date and contract End Date. We can build the formula using DATEDIF.  A great little formula that calculates the number of days, months, or years between two dates.  The Syntax of the formula is

=DATEDIF(Start Date,End Date,interval).  The interval being the type of value to return.  Our options are

In this case we want to choose m for months.

The interval needs to be surrounded by “” marks.  The result of this example is 14 Months.  Let’s say we want to also state how many days as well as months, easy enough, we just need to add in the number of days which is the interval “md” which is the number of calendar days between the dates as if there were of the same year.

The amended formula would be as below.

The result of the new formula is.

By appending the extra & Months and Days text string we can makes the result of the formula look better but part of the formula that extracts the Months and Days is the two DATEDIF statements .  Neat huh?

Have you used the DATEDIF formula?.  Share what you use it for in the comments below 🙂

See More Excel News and Resources

Want Even More Formula Fun?