Method 1 – Using DATEDIF Function
1.1. Applying DATEDIF Function Directly
️ Steps
- Select cell E5, where you want to find the time span as months, and type the following formula in that cell.
=DATEDIF(C5,D5,"M")Here,
- C5 = Launch Date,
- D5 = Closing Date,
- M = The parameter for the number of months to be counted in this function.
- Press Enter and you’ll get the number of months as a time span for the first project in cell E5.
- Drag the Fill Handle tool down to autofill the formula for the other cells.
- All the cells will bring you the number of months from calculating these two dates.
1.2. Customizing DATEDIF Function
NLearn how to customize the DATEDIF function to find out the number of years, months, and days between two dates.
Create the following formula by customizing the DATEDIF function to find the number of months between the encountered dates and apply it to your data.
=DATEDIF(C5,D5,"Y")&" Year(s) "&DATEDIF(C5,D5,"YM")&" Month(s) "& DATEDIF(C5,D5,"MD")&" Day(s)"Here,
- C5 = Launch Date,
- D5 = Closing Date,
- Y = Number of Years,
- MD = Number of Days ignoring Months,
- YM = Number of Months ignoring Years.
️Formula Breakdown
We’re using DATEDIF functions again, but this time, we’ll add some text functions like Year(s) by using Ampersand (&), which will create spaces between words or number(s).
Before 3 units of time, we’re using the DATEDIF function every time to find out the number of years, months, and days separately.
Method 2 – Inserting YEARFRAC Function
2.1. YEARFRAC Wrapped with INT Function
The INT function is used to get the nearest integer. Wrapping up the YEARFRAC function with the INT function will turn the year’s fractional value into an integer.
Apply the following formula to a selected cell.
=INT(YEARFRAC(C5,D5,3)*12)- C5 = Launch Date,
- D5 = Closing Date,
- 3 = 365-day count basis.
️Formula Breakdown
We’re finding out the number of years as time span first which will be shown in decimal format. This value will be multiplied by 12 (No. of months in a year). We’ll use the INT function at the initial to convert decimal into integer format.
You’ll see the same result as found before.
- Drag the formula again like before to find out the number of months as the time span for the rest of the projects.
2.2. YEARFRAC Function Wrapped with ROUNDUP Function
Use the ROUNDUP function instead of the INT function, too, at the initial. But there’s a difference between these 2 functions.
=ROUNDUP(YEARFRAC(C5,D5,3)*12,0)- C5 = Launch Date,
- D5 = Closing Date.
The INT function doesn’t round off the decimal value, so it omits the decimal parts even if it’s too close to the next integer value.
The ROUNDUP function allows you to round off the number up to a fixed decimal place or the nearest whole number, according to your choice.
Method 3 – Combining YEAR and MONTH Functions
Here’s another method you can also use to get the same results. And we’ll combine YEAR and MONTH functions in this method. The YEAR function returns the year of a date, an integer in the range 1900-9999. The MONTH function returns the month, a number from 1 (January) to 12 (December).
Apply the following formula to get the number of months.
=(YEAR(D5)-YEAR(C5))*12+MONTH(D5)-MONTH(C5)- C5 = Launch Date,
- D5 = Closing Date.
️Formula Breakdown
What we’re doing here in cell E5 is-
- Finding the differences between years,
- Converting years into months,
- Adding differences between the ranks or orders of two months.
Method 4 – Subtracting MONTH Functions
We’ll combine multiple MONTH functions with a simple subtraction formula.
Subtract an old date from a new date by using the MONTH function for both dates, and you’re done.
=MONTH(D5)-MONTH(C5)- C5 = Launch Date,
- D5 = Closing Date.
Download Practice Workbook
You can download our Excel workbook which we’ve used to prepare this article. You can also use this practice book as a calculator by inputting dates in the specific fields to work out the number of months between two dates as instructed.
Further Readings
- Excel Formula to Calculate Number of Days Between Today and Another Date
- How to Calculate Expiration Date with Excel Formula
- How to Calculate Years from Today in Excel
- Calculate Number of Days between Two Dates with VBA in Excel
<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
