How to Convert Date to Text Month in Excel (7 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

GIF to Convert Date to Text Month in Excel


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.

Sample Data


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.

Use of TEXT Function to Convert Date to Month in Excel

  • Then, hit the ENTER button.

Converted Order Data to Month by TEXT Function

  • After that, drag the Fill Handle tool to copy the formula for the other cells.

Employing Fill Handle Icon

Lastly, you will get all the name of order Month.

Converting Date to Text Month in Excel

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.

Pressing CTRL+1 to Open Format Cells Dialog Box

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.

Changing Custom Format

Finally, you will get the month names like the image below.

Converting Date to Month by Changing Format in Excel

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.

Using Flash Fill to Extract Month Name from Order Date

As a result, you will observe that all the other cells are filled with corresponding months.

Finding Month from Date with Flash Fill Feature in Excel

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")

Combining SWITCH and MONTH Functions

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

Result after Applying SWITCH & MONTH Functions

  • Now, use the AutoFill option and you will get all the Month name.

Convert Date to Month by Using Excel Functions

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")

Using CHOOSE and MONTH Functions

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

Result after Merging CHOOSE & MONTH Functions

  • Finally, use the Fill Handle tool to copy the formula for the other cells.

Convert Date to Month by Combining CHOOSE & MONTH Functions

Read More: How to Convert Date to Text YYYYMMDD (3 Quick Ways)


Similar Readings


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.

Creating Table from Selected Data Range

As a result, a dialog box named “Create Table” will appear.

  • Now just press OK.

Pressing OK to Create Table Dialog Box

Subsequently, the “Power Query Editor” window will open up.

  • Then, press successively: Transform >> Date >> Month >> Name of Month.

Use of Power Query Editor for Getting Name of Month

So, you will spot that we have found our month names.

  • Now, from Home >> choose Close & Load >> Close & Load.

Loading the Data to New Worksheet

Finally, you will get the name of order month in a new worksheet named Table5.

Loaded Order Month from Power Query to Table5 Worksheet in Excel

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.

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

Setting the Location for Pivot Table

As a result, the “PivotTable Fields” will appear on the right side of your screen.

Inserted Pivot Table

  • Now just mark on the Order Date option from the field and it will automatically show the month names.

Converting Date to Month with the help of PivotTable Fields in Excel

Read More: How to Convert Number to Words in Excel Without VBA


Practice Section

Now, you can practice by yourself.

Practice Section to Convert Order Date to Month


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo