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.
How to Count Months in Excel: 5 Easy Ways
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.
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)
- Finally, press ENTER.
- Then, it will show the month of the C4 cell as I selected that cell.
- Last but not least you can use the Fill Handle to AutoFill the formula for the rest of the cells.
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.
- Later by using the Fill Handle you can AutoFill the formula for the rest of the cells.
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.
- 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.
Then, it will pop up a dialog box. From there first, select Number then select the first format from Negative Numbers.- Finally, click OK.
Then, it will pop up a dialog box. From there first, select Number then select the first format from Negative Numbers.
- Now the year is converted into decimal value.
- Here, you can use the Fill Handle you can AutoFill the formula for the rest of the cells.
- 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.
- Finally, you can use the Fill Handle you can AutoFill the formula for the rest of the cells.
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.
- Later, you can use the Fill Handle you can AutoFill the formula for the rest of the cells.
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:
- 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.
- 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.
Practice
I’ve given a practice sheet in the workbook to practice these explained approaches. You can download it from the above.
Download Practice Workbook
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
<< Go Back to Days Between Dates | Calculate Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
