How to Count Months in Excel(5 ways)

Using DATEDIF

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.
To make the explanation visible I’m going to use a dataset of project information when it started and when finished. There are 3 columns which are Project Name, Start Date, and End Date.

Sample Dataset

Download to Practice

5 Ways to Count Months in Excel

1. Using MONTH

To count the month from a date you can use the MONTH function.
First, select the cell where you want to place your resultant value.
➤ I selected the cell D4
Now type the formula in the selected cell or in the Formula Bar.
The Formula is

=MONTH(C4)

Using MONTH

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

Using MONTH

Last but not least you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Using MONTH

2. Using DATEDIF

You can use the DATEDIF function to count months in Excel.
Firstly, select the cell where you want to keep your result.
➤ I selected the 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

 

Using DATEDIF

Lastly, press ENTER.
After that, it will show the months between the Start Date and End Date.

Using DATEDIF

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

Using DATEDIF

3. Using YEARFRAC

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.
For that, you need to select a cell first, to put your resultant value.
➤ I selected the cell E4
After that, type the formula either in the selected cell or in the Formula Bar.
The Formula is

=(YEARFRAC(C5,D5)*12)

Using YEARFRAC

Finally, press ENTER.
As a result, it will show the result in date format.

Using YEARFRAC

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

Using YEARFRAC

Then, it will pop up a dialog box. From there first, select Number then select the first format from Negative Numbers.
Finally, click OK.

Using YEARFRAC

Now the year is converted into decimal value.

Using YEARFRAC

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

Using YEARFRAC

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 the cell F4
Then, type the formula either in the selected cell or in the Formula Bar.
The Formula is

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

Using YEARFRAC with INT

Next, press ENTER.
You will get the round-up value in the Months column.

Using YEARFRAC with INT

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

Using YEARFRAC with INT

4. Using YEAR and MONTH

You can use the YEAR and MONTH function together to count months in Excel.
First, select the cell to place your counted months.
➤ I selected the 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 of start and end year is multiplied by 12 then the difference of start and end months is summed to count months.

Using YEAR and MONTH

Last, press ENTER.
You will get the counted months of both start and end dates.

Using YEAR and MONTH

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

Using YEAR and MONTH

5. Using COUNTIF to COUNT by Months

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

Using COUNTIF to COUNT by Months

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 the cell D4
Then type the formula in the selected cell or in the Formula Bar.
The Formula is

=COUNTIF(D$4:D$10,MONTH(F4))

Using COUNTIF

Now, press ENTER
Finally, it will count the selected month and will show you the result in the G4 cell.

Using COUNTIF to COUNT by Months

Here the value 2 in the Count column represents that the month of January appeared twice in the Start Date column.
Now you can use the Fill Handle to AutoFit the formula for the rest of the cells.

Using COUNTIF to COUNT by Months

Practice

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

practice sheet

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.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. 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. 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