How to Convert Month to Number in Excel (3 Easy Methods)

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.

• 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.

• 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.

• 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