Method 1 – Inserting MONTH Function to Convert Month to Number in Excel
1.1. Use of MONTH Function
Steps:
- Select a cell, we have selected cell (D5).
- Enter the following formula in the selected cell.
=MONTH(C5&1)- The MONTH function returns the number between 1 to 12 from a given date.
- Press Enter.
- Use the Fill Handle tool for the remaining cells.
- All the months will be converted to numbers.
1.2. Combine MONTH and DATEVALUE Functions
Steps:
- Select a cell. We have selected cell (D5).
- Enter the following formula in the selected cell.
=MONTH(DATEVALUE(C5&1))- The DATEVALUE function returns a date-time code within the given string.
- Press Enter.
- Drag the “Fill Handle” down to get the output.
- All the months will be converted to numbers.
Method 2 – Applying Excel TEXT Function to Convert Month to Number
Step 1:
- Select the dates and press Ctrl+1.
- A new window named “Format Cells” will pop up.
- Select Custom and enter “mmmm” in the “Type” section.
- Click OK.
- Only the month’s name will be displayed.
- We will convert them to numbers.
Step 2:
- Enter the following formula in the cell D5.
=TEXT(C5,"m")- The TEXT function converts a numeric value to a specific format in a given string.
- Press the Enter button and drag the “Fill Handle” down to fill all the cells.
- We have converted the month to number using the TEXT function.
Read More: How to Calculate First Day of Previous Month in Excel
Method 3 – Converting Month to Number with the Format Cells Feature
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 enter “mmmm” in the “Type” section.
- Click OK.
- We will get only the month name in the selected cells. If you select any month name from the table, it will show the date in the formula bar.
Step 2:
- Select the month names from the table and press Ctrl+1 to open the “Format Cells” option.
- Choose “Custom” and enter “m” in the “Type” section.
- Click OK.
- 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
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
<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
