How to Count Months in Excel (5 Easy Ways)

We used the functions COUNTIF and MONTH to count the month between dates, and here’s the overview.

overview of counting months in excel using countif and month functions

How to Count Months in Excel: 5 Easy Ways

We have a simple dataset with project names and their start and end dates.

Sample Dataset in order to count months in Excel

Method 1 – Insert the MONTH Function to Get the Month Number

Steps:

Insert the following formula in the first result cell (D4).

=MONTH(C4)

Using MONTH Function to Get the MONTH Count

Hit Enter.

Using MONTH Function IN CELL D4

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Dragging the fill handle to cell D10 to fill the range of cell D4:D10.

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.

Using DATEDIF Function in Cell E4 to Get the MONTH Count

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Use of Fill Handle to cell E10 to get month count

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.

Use of YEARFRAC Function to get the MONTH Count

Select the E4 cell.
Open the Home tab and select the Down Arrow on the Number group.

Reformat the cell from date to Number format

This will open a Format Cells dialog box. Select Number, then select the first format from Negative Numbers.
Click OK.

Formatting the cell using the Format Cells dialog box

The year is converted into a decimal value.

Cell value is now changed to number format from the date format.

You can use the Fill Handle you can AutoFill the formula for the rest of the cells.

Dragging the Fill handle to cell E10 in order to get the month count in between date

Insert the following formula in F4.

=INT(YEARFRAC(C4,D4)*12)

Hit Enter.
You will get the round-up value in the Months column.

count of month in integer form

Use the Fill Handle to AutoFill the formula for the rest of the cells.

The autofill feature used to fill the range of cell F4:F10 with integer month value

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.

Combination of the YEAR and the MONTH functions to Get the MONTH Count

Use the Fill Handle to AutoFill the formula for the rest of the cells.

$The range of cell is now filled with the integer value of the month fraction$

Method 5 – Count Months Through Excel COUNTIF and MONTH Functions

Steps:

Template of the dataset that are going to be used to get the month count

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.

Using the combination of COUNTIF and MONTH Function to COUNT Months

Use the Fill Handle to AutoFill the formula for the rest of the cells.

Dragging the Fill Handle to cell G7

Practice Section

We’ve provided a practice dataset where you can test these methods.

Practice worksheet where to implement functions to get the month count

Download the Practice Workbook

Count Months.xlsx

How to Count Months in Excel (5 Easy Ways)

How to Count Months in Excel: 5 Easy Ways

Method 1 – Insert the MONTH Function to Get the Month Number

Method 2 – Use the DATEDIF Function in Excel to Count Months

Method 3 – Apply the YEARFRAC Functions to Get the Month Count

Method 4 – Using YEAR and MONTH Functions to Count Months Between Dates

Method 5 – Count Months Through Excel COUNTIF and MONTH Functions

Practice Section

Further Readings

Leave a reply Cancel reply