Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Months in Excel (5 Easy Ways)

When we want to keep track of a particular project and work, and many more then it is important to count the months. To keep track we need to count the month from the start date to the end date. In this article, I’m going to explain how to count months in Excel.

Below, we used the functions COUNTIF and MONTH to count the month between dates. We will demonstrate more methods in order to get the month’s count.

overview of counting months in excel using countif and month functions


Download Practice Workbook


5 Easy Ways to Count Months in Excel

To make the explanation visible I’m going to use a dataset of project information on when it started and when finished. There are 3 columns which are Project Name, Start Date, and End Date. In order to avoid any compatibility issues, try to use the Excel 365 edition.

Sample Dataset in order to count months in Excel


1. Insert MONTH Function to Get Month Count

To count the month from a date you can use the MONTH function.

Steps:

  • First, select the cell where you want to place your resultant value.
  • I selected cell D4.
  • Now type the formula in the selected cell or in the Formula Bar.
  • The Formula is:
=MONTH(C4)

Using MONTH Function to Get the MONTH Count

  • Finally, press ENTER.
  • Then, it will show the month of the C4 cell as I selected that cell.

Using MONTH Function IN CELL D4

  • Last but not least you can 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.

Read More: How to Add Months to a Date in Excel (2 Ways)


2. Use DATEDIF Function in Excel to Count Months

You can use the DATEDIF function to count months in Excel.

Steps:

  • Firstly, select the cell where you want to keep your result.
  • I selected cell E4.
  • Secondly, type the formula either in the selected cell or in the Formula Bar.
  • The Formula is:
=DATEDIF(C4,D4,"M")
  • Here M is for Month.
  • Lastly, press ENTER.
  • After that, it will show the months between the Start Date and End Date.

Using DATEDIF Function in Cell E4 to Get the MONTH Count

  • Later by using the Fill Handle you can AutoFill the formula for the rest of the cells.

Use of Fill Handle to cell E10 to get month count

Read More: How to Calculate Number of Months Between Two Dates in Excel


3. Apply YEARFRAC Functions in Order to Get Month Count 

You also can use the YEARFRAC function to count the months in Excel. To count months using YEARFRAC you need to multiply the result by 12 to convert it into months.

Steps:

  • For that, you need to select a cell first, to put your resultant value.
  • I selected cell E4.
  • After that, type the formula either in the selected cell or in the Formula Bar.
  • The Formula is:
=(YEARFRAC(C5,D5)*12)
  • Finally, press ENTER.
  • As a result, it will show the result in date format.

Use of YEARFRAC Function to get the MONTH Count

  • To calculate the fractional year in a decimal format first, select the E4 cell.
  • Second, open the Home tab >> From Number group >> select the Down Arrow.
  • Reformat the cell from date to Number formatThen, it will pop up a dialog box. From there first, select Number then select the first format from Negative Numbers.
  • Finally, click OK.

Formatting the cell using the Format Cells dialog box

  • Now the year is converted into decimal value.

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

  • Here, 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

  • In case you want to round up the value then you can use the INT function in the YEARFRAC function.
  • Now select the cell to keep your round-up result.
  • I selected cell F4.
  • Then, type the formula either in the selected cell or in the Formula Bar.
  • The Formula is:
=INT(YEARFRAC(C4,D4)*12)
  • Moreover, press ENTER.
  • You will get the round-up value in the Months column.

count of month in integer form

  • Finally, you can use the Fill Handle you can 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

Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)


Similar Readings


4. Using YEAR and MONTH Functions to Count Months

You can use the YEAR and MONTH functions together to count months in Excel.

Steps:

  • First, select the cell to place your counted months.
  • I selected cell D4.
  • Second, type the formula in the selected cell or in the Formula Bar.
  • The Formula is:
  • =(YEAR(D4)-YEAR(C4))*12+MONTH(D4)-MONTH(C4)
  • Here the difference between the start and end year is multiplied by 12 then the difference between the start and end months is summed to count months.
  • Last, press ENTER.
  • You will get the counted months of both start and end dates.

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

  • Later, you can use the Fill Handle you can AutoFill the formula for the rest of the cells.

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

Read More: How to Calculate Years Between Two Dates in Excel (2 Methods)


5. Count Months Through Excel COUNTIF and MONTH Functions

To show the use of the COUNTIF function I’ve added two extra columns to the dataset. These are Date-Month and Months.

Steps:

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

  • Here, I got the values of Date-Month using the MONTH function. If you want you can see it again from the Using MONTH section.
  • To count the month from a date you can use the COUNTIF function.
  • To begin with, select the cell where you want to place your resultant value.
  • I selected cell D.
  • Then type the formula in the selected cell or in the Formula Bar.
  • The Formula is:
=COUNTIF(D$4:D$10,MONTH(F4))
  • Now, press ENTER.
  • Finally, it will count the selected month and will show you the result in the G4 cell.

Using the combination of COUNTIF and MONTH Function to COUNT Months 

  • Here the value 2 in the Count column represents that the month of January appeared twice in the Start Date column.
  • Finally, now you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Dragging the Fill Handle to cell G7

Read More: How to Calculate Working Days in a Month in Excel (4 Easy Ways)


Practice

I’ve given a practice sheet in the workbook to practice these explained approaches. You can download it from the above.

Practice worksheet where to implement functions to get the month count


Conclusion

In this article, I’ve explained 5 ways to count months in Excel. These different approaches will help you to months from a date as well as the difference between two dates. Feel free to comment down below to give any kind of suggestions, ideas, and feedback.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo