We used the functions COUNTIF and MONTH to count the month between dates, and here’s the overview.
How to Count Months in Excel: 5 Easy Ways
We have a simple dataset with project names and their start and end dates.
Method 1 – Insert the MONTH Function to Get the Month Number
Steps:
- Insert the following formula in the first result cell (D4).
=MONTH(C4)
- Hit Enter.
- Use the Fill Handle to AutoFill the formula for the rest of the cells.
Method 2 – Use the DATEDIF Function in Excel to Count Months
Steps:
- Insert the following formula in the first result cell (E4).
=DATEDIF(C4,D4,"M")- Hit Enter and the formula will return the number of months between the values.
- Use the Fill Handle to AutoFill the formula for the rest of the cells.
Method 3 – Apply the YEARFRAC Functions to Get the Month Count
Steps:
- Insert the following formula in the first result cell (E4).
=(YEARFRAC(C5,D5)*12)- Hit Enter.
- Select the E4 cell.
- Open the Home tab and select the Down Arrow on the Number group.
- This will open a Format Cells dialog box. Select Number, then select the first format from Negative Numbers.
- Click OK.
- The year is converted into a decimal value.
- You can use the Fill Handle you can AutoFill the formula for the rest of the cells.
- Insert the following formula in F4.
=INT(YEARFRAC(C4,D4)*12)- Hit Enter.
- You will get the round-up value in the Months column.
- Use the Fill Handle to AutoFill the formula for the rest of the cells.
Method 4 – Using YEAR and MONTH Functions to Count Months Between Dates
Steps:
- Insert the following formula in the first result cell (E4).
=(YEAR(D4)-YEAR(C4))*12+MONTH(D4)-MONTH(C4)- Hit Enter.
- Use the Fill Handle to AutoFill the formula for the rest of the cells.
Method 5 – Count Months Through Excel COUNTIF and MONTH Functions
Steps:
- We have the values of Date-Month using the MONTH function in column D (based on values in column C).
- Insert the following formula in the first result cell (G4).
=COUNTIF(D$4:D$10,MONTH(F4))- Hit Enter.
- Use the Fill Handle to AutoFill the formula for the rest of the cells.
Practice Section
We’ve provided a practice dataset where you can test these methods.
Download the Practice Workbook
Further Readings
<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
