When we enter a date in Excel then it is possible to extract the month name from the date. This article will guide you with 8 quick useful methods to convert date to month as text in Excel.
Download Practice Book
You can download the free Excel template from here and practice on your own.
8 Quick Methods to Convert Date to Text Month in Excel
Method 1: Use TEXT Function to Convert Date to Text Month in Excel
Let’s get introduced to our dataset first. I have placed some order IDs’ and their order dates in the dataset. Now we’ll use the TEXT function to convert the dates to month as text. The Excel TEXT function is used to convert numbers to text within a spreadsheet.
I have added a new column named “Month” to show the month names.
➤ Type the given formula in Cell D5–
➤ Then hit the Enter button and use the Fill Handle tool to copy the formula for the other cells.
Method 2: Apply Formatting Option to Switch Date to Text Month in Excel
Here, I’ll use the Excel “Formatting Cells” option to convert the date to text month.
➤ Copy the dates to the Month column.
➤ Then select the copied date range.
➤ Press the arrow icon from the Alignment bar.
The “Formatting Cells” dialog box will open up.
➤ Select Custom
➤ Write “mmmm” on the Type bar.
➤ Then press OK.
Now you will get the month names like the image below.
Method 3: Use Flash Fill to Convert Date to Text Month in Excel
In this method, we’ll apply the Excel Flash Fill tool to do the same operation. It’s one of the easiest ways. If the date is in Long Date format then it will be helpful.
➤ At first, write the first month’s name.
➤ Then select it and click as follows: Data > Data Tools > Flash Fill
Now you will observe that all the other cells are filled with corresponding months.
Method 4: Insert SWITCH And MONTH Functions Together to Alter Date to Text Month in Excel
Now we’ll convert date to text month by using the combination of the SWITCH function and the MONTH function. The SWITCH function evaluates one value against a list of values and returns the result according to the first matching value. And the MONTH function gives the month of the given date or serial number.
➤ Activate Cell D5
➤ Type the formula-
➤ After that just press the Enter button and use the AutoFill option.
👇 How Does the Formula Work?
The MONTH function will extract the month number from the date in Cell C5 that will return as-
Then the SWITCH function will replace that number according to our given month name in the formula. It will return as-
Method 5: Combine CHOOSE and MONTH Functions to Convert Date to Text Month in Excel
Let’s use another combination of functions to convert date to text month. We’ll use the CHOOSE and MONTH functions. The CHOOSE function is used to return a value from the list based on a specified position.
➤ By activating Cell D5 type the given formula-
➤ Finally, hit the Enter button and use the Fill Handle tool to copy the formula for the other cells.
👇 Breakdown of the Formula:
The MONTH function will give the month number from the date in Cell C5 that will return as-
Then the CHOOSE function will switch the number according to the given month name in the formula. That will return as-
Method 6: Use Power Query to Transform Date to Text Month in Excel
Power Query is a tool in Excel that simplifies the process of importing data from different sources. In this method, we’ll operate it to convert date to text month.
➤ Select the date range.
➤ Click serially: Data > From Table/Range
A dialog box named “Create Table” will appear.
➤ Now just press OK.
A “Power Query Editor” window will open up.
➤ Then press successively: Transform > Date > Month > Name of Month
Now you will spot that we have found our month names.
Method 7: Create Pivot Table to Convert Date to Text Month in Excel
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. We can do the operation using Pivot Table too.
➤ Select the range of your dataset.
➤ Then click- Insert > Pivot Table
A dialog box named “Create PivotTable” table will appear.
➤ Now select your desired sheet and location. I have selected Existing Worksheet and Cell E4 as location.
➤ Press OK.
The “PivotTable Fields” will appear on the right side of your screen.
➤ Now just mark on the Date option from the field and it will automatically show the month names.
Method 8: Create Power Pivot Table to Switch Date to Text Month in Excel
In our last method, We’ll use the Pivot Table in a different way that is called Power Pivot Table.
The first 2 steps are like the previous method.
➤ Then put a mark on “Add this data to the Data Model” from the “Create PivotTable” Dialog Box.
➤ After that click as follows: Power Pivot > Manage
A new window named “Power Pivot” will appear.
In that window, I have added a new column named “Month”
➤ Click on that column and type the given formula:
Finally, Hit the Enter button to get month names.
Now we have found our expected month names.
I hope all of the methods described above will be useful enough to convert date to text month in Excel. Feel free to ask any questions in the comment section and please give me feedback.