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 7 quick useful methods to convert date to month as text in Excel.
Below, we have added an animation as the overview of this article.
Let’s get introduced to our dataset first. Here, we have placed some order IDs’ and their order dates in the dataset. At this time, we want to extract the name of month from the order date.
Furthermore, for conducting this session, we’re going to use Microsoft Office 365 version.
1. Using TEXT Function to Convert Date to Text Month in Excel
We’ll use the TEXT function to convert the dates to months as text. The TEXT function is used to convert numbers to text within a spreadsheet. So, we have added a new column named Month to show the month names.
Steps:
- First, type the given formula in cell D5.
=TEXT(C5,"mmmm")Here, the TEXT function will format the value of cell C5 as mmmm or the name of the month.
- Then, hit the ENTER button.
- After that, drag the Fill Handle tool to copy the formula for the other cells.
Lastly, you will get all the names of months.
Read More: How to Use Formula to Change Date Format in Excel
2. Applying Format Cells Feature to Change Date to Text Month in Excel
Here, we’ll use the Excel Format Cells feature to convert the date to text month.
Steps:
- Copy the dates to the Month column.
- Select the copied date range >> press CTRL+1.
As a result, the Format Cells dialog box will open up.
- Now, select Custom >> Write mmmm on the Type bar.
- After that, press OK.
Finally, you will get the month names like the image below.
Read More: How to Convert Date to Text YYYYMMDD
3. Using 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. Actually, it’s one of the easiest ways. If the date is in Long Date format, then it will be helpful. Now, let’s see the steps below.
Steps:
- First, write the first month’s name. Here, we have to write January manually.
- Then, select it and click as follows: Data >> Data Tools >> Flash Fill.
As a result, you will observe that all the other cells are filled with corresponding months.
4. Combining SWITCH and MONTH Functions to Change Date to Text Month in Excel
Now we’ll convert date to text month by using the combination of SWITCH and MONTH functions. Basically, the SWITCH function takes one single expression for matching with a given value and converts that particular value to a given format. However, this SWITCH function will consider the matching value which comes first. And the MONTH function gives the month of the given date or serial number which will act as that expression of the SWITCH function.
Steps:
- Now, activate cell D5.
- Then, type the formula below.
=SWITCH(MONTH(C5),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
- After that just press the ENTER button.
👇 How Does the Formula Work?
➥ MONTH(C5)
Here, the MONTH function will extract the month number from the date in cell C5 that will return as-
{1}
➥ SWITCH(MONTH(C5),1,”January”,2,”February”,3,”March”,4,”April”,5,”May”,6,”June”,7,”July”,8,”August”,9,”September”,10,”October”,11,”November”,12,”December”)
Then the SWITCH function will replace that number according to our given month name in the formula. Lastly, it will return as-
{January}
- Now, use the AutoFill option and you will get all the month names.
Read More: How to Remove Year from Date in Excel
5. Applying Excel CHOOSE and MONTH Functions to Convert Date to Text Month
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.
Steps:
- First, by activating cell D5 type the given formula-
=CHOOSE(MONTH(C5),"January","February","March","April","May","June","July","August","September","October","November","December")
- Consequently, hit the ENTER button.
👇 Breakdown of the Formula:
➥ MONTH(C5)
Here, the MONTH function will give the month number from the date in cell C5 that will return as-
{1}
➥CHOOSE(MONTH(C5),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,”October”,”November”,”December”)
Then the CHOOSE function will switch the number according to the given month name in the formula. Finally, that will return as-
{January}
- Use the Fill Handle tool to copy the formula for the other cells.
Read More: How to Convert Date to Julian Date in Excel
6. Using 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. Now, in this method, we’ll operate it to convert date to text month.
Steps:
- Select the date range.
- Then, click serially: Data >> From Table/Range.
As a result, a dialog box named Create Table will appear.
- Now just press OK.
Subsequently, the Power Query Editor window will open up.
- Then, press successively: Transform >> Date >> Month >> Name of Month.
So, you will spot that we have found our month names.
- Now, from Home >> choose Close & Load >> Close & Load.
Finally, you will get the name of the months in order in a new worksheet named Table5.
Read More: How to Convert 7-Digit Julian Date to Calendar Date in Excel
7. Creating Pivot Table to Convert Date to Text Month in Excel
The PivotTable is a tool to find patterns, trends, and comparisons in data by summarizing and analyzing them. Actually, using this feature, you can analyze tons of data with just a few mouse clicks. Here, we can do the operation using Pivot Table too.
Steps:
- First, select the range of your dataset.
- Then click- Insert >> Pivot Table.
So, a dialog box named PivotTable from table or range will appear.
- Now select your desired sheet and location. Here, we have selected the Existing Worksheet and cell E4 as the location.
- Then, press OK.
As a result, the PivotTable Fields will appear on the right side of your screen.
- Now just mark the Order Date option from the field and it will automatically show the month names.
Read More: Stop Excel from Converting Date to Number in Formula
Download Practice Book
You can download the free Excel template from here and practice on your own.
Conclusion
So, we 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.
