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.
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.
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.
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.
- Later by using the Fill Handle you can AutoFill the formula for the rest of the cells.
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.
- 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.
- 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.
Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)
Similar Readings
- Excel Formula to Count Days from Date (5 Easy Methods)
- How to Add Years to a Date in Excel (3 Easy Ways)
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Calculate Tenure in Years and Months in Excel
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.
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:
- 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.
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.
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
- How to Calculate Years in Excel from Today (4 Ways)
- Excel Formula to Find Date or Days for Next Month (6 Quick Ways)
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Excel Formula to Calculate Number of Days Between Today and Another Date
- How to Add Weeks to a Date in Excel (4 Simple Methods)