Md. Sourov Hossain Mithun
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.
Download Practice Book
You can download the free Excel template from here and practice on your own.
7 Quick Methods to Convert Date to Text Month in Excel
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 365 version.
1. Using TEXT Function to Convert Date to Text Month in Excel
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. So, we have added a new column named “Month” to show the month names.
Steps:
- Firstly, type the given formula in Cell D5–
=TEXT(C5,"mmmm")Here, the TEXT function will format the C5 cell value as “mmmm” or name of 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 name of order Month.
Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)
2. Applying Format Cells Feature to Switch Date to Text Month in Excel
Here, we’ll use the Excel “Format Cells” feature to convert the date to text month.
Steps:
- Firstly, copy the dates to the Month column.
- Secondly, select the copied date range >> then press CTRL+1.
As a result, the “Format Cells” dialog box will open up.
- Now, select Custom >> then 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 Number to Text in Excel with Apostrophe
3. Employing 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:
- At 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.
Read More: How to Convert Number to Text with Green Triangle in Excel
4. Merging SWITCH And MONTH Functions 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. 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 that 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 SWITCH function.
Steps:
- Now, activate Cell D5.
- Then, type the formula-
=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 name.
Read More: How to Convert Number to Words in Excel (4 Suitable Ways)
5. Combining 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.
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}
- Finally, use the Fill Handle tool to copy the formula for the other cells.
Read More: How to Convert Date to Text YYYYMMDD (3 Quick Ways)
Similar Readings
- How to Convert Number to Text with Leading Zeros in Excel
- Convert Text to Numbers in Excel (8 Easy Ways)
- How to Convert Number to Text with Commas in Excel (3 Easy Methods)
- Excel VBA to Convert Number to Text (4 Examples)
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:
- At first, 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 order month in a new worksheet named Table5.
Read More: How to Convert a Numeric Value into English Words in Excel
7. Creating Pivot Table to Convert Date to Text Month in Excel
A 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 Existing Worksheet and Cell E4 as location.
- Then, press OK.
As a result, the “PivotTable Fields” will appear on the right side of your screen.
- Now just mark on the Order Date option from the field and it will automatically show the month names.
Read More: How to Convert Number to Words in Excel Without VBA
Practice Section
Now, you can practice by yourself.
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. Additionally, you can visit our website Exceldemy to learn more Excel-related content.
Related Articles
- How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)
- Convert Number to Text without Scientific Notation in Excel
- How to Convert Number to Text for VLOOKUP in Excel (2 Ways)
- Convert Number to Text and Keep Trailing Zeros in Excel (4 Ways)
- How to Convert Number to Text in Excel (4 Ways)
- Convert Peso Number to Words in Excel (With Easy Steps)
- How to Convert Number to Words in Excel in Rupees
