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

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.

TEXT Function to Convert Date to Text Month in Excel

I have added a new column named “Month” to show the month names.

Step 1:

➤ Type the given formula in Cell D5

=TEXT(C5,"mmmm")

TEXT Function to Convert Date to Text Month in Excel

Step 2:

➤ Then hit the Enter button and use the Fill Handle tool to copy the formula for the other cells.

Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)


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.

Step 1:

➤ Copy the dates to the Month column.

Formatting Option to Switch Date to Text Month in Excel

Step 2:

➤ Then select the copied date range.

➤ Press the arrow icon from the Alignment bar.

The “Formatting Cells” dialog box will open up.

Formatting Option to Switch Date to Text Month in Excel

Step 3:

➤ Select Custom 

➤ Write “mmmm” on the Type bar.

➤ Then press OK.

Now you will get the month names like the image below.

Read More: How to Convert Number to Text in Excel with Apostrophe


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.

Steps:

➤ At first, write the first month’s name.

➤ Then select it and click as follows: Data > Data Tools > Flash Fill

Flash Fill to Convert Date to Text Month in Excel

Now 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


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.

Step 1:

➤ Activate Cell D5

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

SWITCH And MONTH Functions Together to Alter Date to Text Month in Excel

Step 2:

➤ After that just press the Enter button and use the AutoFill option.

👇 How Does the Formula Work?

MONTH(C5)

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. It will return as-

{January}

Read More: How to Convert Number to Words in Excel (4 Suitable Ways)


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.

Step 1:

➤ By activating Cell D5 type the given formula-

=CHOOSE(MONTH(C5),"January","February","March","April","May","June","July","August","September","October","November","December")

CHOOSE and MONTH Functions to Convert Date to Text Month in Excel

Step 2:

➤ Finally, hit the Enter button and use the Fill Handle tool to copy the formula for the other cells.

👇 Breakdown of the Formula:

MONTH(C5)

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. That will return as-

{January}

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


Similar Readings


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.

Step 1:

➤ Select the date range.

➤ Click serially: Data > From Table/Range

A dialog box named “Create Table” will appear.

Power Query to Transform Date to Text Month in Excel

Step 2:

➤ Now just press OK.

A “Power Query Editor” window will open up.

Power Query to Transform Date to Text Month in Excel

Step 3:

➤ Then press successively: Transform > Date > Month > Name of Month

Power Query to Transform Date to Text Month in Excel

Now you will spot that we have found our month names.

Read More: How to Convert a Numeric Value into English Words in Excel


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.

Step 1:

➤ Select the range of your dataset.

➤ Then click- Insert > Pivot Table

A dialog box named “Create PivotTable” table will appear.

Pivot Table to Convert Date to Text Month in Excel

Step 2:

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

Pivot Table to Convert Date to Text Month in Excel

Step 3:

➤ Now just mark on the 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


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.

Step 1:

➤ Then put a mark on “Add this data to the Data Model” from the “Create PivotTable” Dialog Box.

Power Pivot Table to Switch Date to Text Month in Excel

Step 2:

➤ After that click as follows: Power Pivot > Manage

A new window named “Power Pivot” will appear.

Power Pivot Table to Switch Date to Text Month in Excel

In that window, I have added a new column named “Month

Step 3:

➤ Click on that column and type the given formula:

=FORMAT(Range[Date],”mmmm”)

Finally, Hit the Enter button to get month names.

Now we have found our expected month names.

Read More: How to Convert Number to Text in Excel (4 Ways)


Conclusion

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.


Related Articles

Mithun

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

ExcelDemy
Logo