How to Convert Date to Month in Excel (6 Easy Methods)

We will be working on the following dataset.

Convert a Date to Month in Excel: 6 Methods

Method 1 – Converting a Date to the Month with the Month Function

Steps:

• Use the following formula in cell C5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

Read More: How to Convert Date to Day of Week in Excel

Method 2 – Implementing a Custom Format

Steps:

• Select the range C5:C9 (Where you want to keep the name of the month).
• Click on the drop-down symbol of the Number group.

• Click on More Number Formats.

• A new window will appear named Format Cells.
• Select Custom and type mmmm in the Type menu.
• Click OK.

• Use this formula in cell C5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

We’ll also show the months as number:

• Select the range D5:D9 and go to the Number group again.

• Select More Number Formats…

• A new window will appear. Select Custom and type mm in the Type menu.
• Click OK.

• Use the following formula in cell D5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

You can also get the month from the date by choosing the Date type from the Format Number ribbon:

• Select the cells B5:B9 and then select More Number Formats. Select the date format which shows the month. Then, click OK.

Here’s our result.

Method 3 – Applying the CHOOSE Function to Convert Dates to Months

Steps:

• Use the following formula in cell C5. You may type the whole names of the months if you want.

The MONTH function returns the position number of a month in the calendar. The CHOOSE function takes this number as an index, then returns the value according to the index. Each value will return the name of the month.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

• To get the month number from these dates, modify the current formula like below.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

Method 4 – Utilizing the SWITCH Function to Convert Dates to Months

Steps:

• Use the following formula in cell C5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

• To see the month number, modify the formula to the following:

• Hit Enter.

• Use the Fill Handle to AutoFill down.

Read More: How to Convert Date to Month and Year in Excel

Method 5 – Using the TEXT Function to Convert Dates to Months

Steps:

• Use this simple formula in cell C5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

• If you want to extract only the number of months from this data, use the following formula in cell D5.

• Hit Enter.

• Use the Fill Handle to AutoFill down.

Related Content: How to Convert Date to Number in Excel

Method 6 – Using Excel Power Query to Convert Dates to Months

Steps:

• Select the range B4:B9.
• Go to Data.
• Select From Range/Table.

• A dialog box will appear. Click OK.
• Make sure My table has headers is selected.

• You will see a new window for the Power Query Editor containing the Date column. We get a time of 12:00:00 AM by default.

• Select the header (Date) and then go to Add Columns, then to Date, Month, and Name of Month.

• This will show you the names of the months of the corresponding dates.

• Go to Add Columns, then to Date, Month, and Month.

• You will see the month number in a new column.

• Go to the Home tab of the Power Query Editor and select Close & Load.

• You will see a table in a new sheet.

Practice Section

We’ve included a practice dataset you can use to test the methods.

Download the Practice Workbook

Related Articles

• How to Convert Date to Year in Excel
• How to Convert Date to Quarter and Year in Excel
• How to Convert Date to Day of Year in Excel
• How to Convert Date to Day in Excel

<< Go Back to Date Format | Number Format | Learn Excel