While working in Microsoft Excel we might need to convert month to number for different purposes. Sometimes we need to convert to numbers for calculation advantages. In this article, I will share how to convert month to number in Excel.
In the following article, I have described 3 simple and easy methods to convert month to number in Excel.
Suppose we have a dataset of some months’ names. Now we will convert these months to numbers using some functions and Excel’s built-in features.
1. Inserting MONTH Function to Convert Month to Number in Excel
If you want to get a good result in a quick way then you must use functions to convert month to number in Excel. Below I have converted to numbers applying two different functions.
1.1. Use of MONTH Function
In this method, I have applied the MONTH function to convert month to number. Follow the steps below-
Steps:
- In the first place, choose a cell. Here I have selected cell (D5).
- Then, apply the following formula-
=MONTH(C5&1)
Where,
- The MONTH function returns the number between 1 to 12 from a given date.
- After that, press Enter.
- Further, drag the “Fill Handle” to fill all the cells.
- As you can see we have successfully converted months to numbers performing a simple and easy function.
1.2. Combine MONTH and DATEVALUE Functions
You can get your desired result using the MONTH function and the DATEVALUE function.
Steps:
- Select the cell where you want to write the formula. For instance, we select cell (D5).
- After that, put the formula down-
=MONTH(DATEVALUE(C5&1))
Where,
- The DATEVALUE function returns a date-time code within the given string.
- Hit the Enter.
- Pull the “Fill Handle” down to get the output.
- Here we have our desired output in our hands with conversion to numbers.
Read More: Get the First Day of the Current Month in Excel
2. Applying Excel TEXT Function to Convert Month to Number
While working in Microsoft Excel you might find dates in various cells. Now you must be thinking about how you will convert them. Don’t worry. Go through the following steps to find your solution.
Step 1:
- First, choose those dates and press Ctrl+1.
- Accordingly, a new window will open named “Format Cells”.
- Now, select Custom and write “mmmm” in the “Type” section.
- Click OK to continue.
- Now we have only the month’s name in our hands.
- Let’s convert them to numbers.
Step 2:
- Choose a cell. Here I have selected cell (D5).
- Then, write the formula down-
=TEXT(C5,"m")
Where,
- The TEXT function converts a numeric value to a specific format in a given string.
- Click the Enter button and drag the “Fill Handle” down to fill all the cells.
- Finally, we have converted the month to number using the TEXT function in Excel. Simple isn’t it?
Read More: How to Calculate First Day of Previous Month in Excel
3. Converting Month to Number with the Format Cells Feature
If you are looking for a way to convert month to number without using functions, then you are at the right place. You can use the format cells feature to convert month to number in Excel.
Step 1:
- Select the formatted dates from the table.
- Click the icon of format cells from the ribbon.
- From the pop-up window choose “Custom” and then write down “mmmm” in the “Type” section.
- Subsequently, press OK to continue.
- Likewise the previous method, we will get the month name only in the selected cells. Check out the screenshot, if you select any month name from the table it will show the date in the formula bar.
Step 2:
- Now choosing the month names from the table press Ctrl+1 to open the “Format Cells” option again.
- Here we will choose “Custom” and this time we will put “m” in the “Type” section.
- Furthermore, hit the OK button to continue.
- Finally, we have converted months to numbers without applying functions.
Things to Remember
The TEXT function won’t work if you apply it over a month’s name directly. The TEXT function works over numeric dates only.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover all the simple methods to convert month to number in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience.
Related Articles
- How to Get First Day of Month from Month Name in Excel
- How to Get Last Day of Previous Month in Excel
- How to Get Last Day of Previous Month in Excel
- Excel Formula for Current Month and Year
- Convert 3 Letter Month to Number in Excel
- Excel VBA: First Day of Month
- How to Get the Last Day of Month Using VBA in Excel