How to Convert Date to Month in Excel (6 Easy Methods)

This article will show you some interesting ways to convert date to month in Excel. It is one of the easiest tasks in Microsoft Excel. We will be working on the following dataset.

convert date to month in excel


Download Practice Workbook


6 Ways to Convert Date to Month in Excel

1. Converting Date to Month by Using Month Function

This is the simplest way to convert a date to its corresponding month. By the way, you will get the month number using the MONTH function.

Steps:

  • Type the following formula in cell C5.
=MONTH(B5)

Here the MONTH function simply returns the value of the month which carries the date.

  • Now press ENTER and you will see the month number in cell C5.

convert date to month in excel

  • After that, use the Fill Handle to AutoFill the lower cells.

By using this simple function, you can easily convert date to month in Excel.

Read More: How to Convert Date to Day of Week in Excel (8 Methods)


2. Implementing Command from Custom Format

Another easy way to convert date to month is to change the number format. Let’s discuss the procedure.

Steps:

  • At the very beginning, you need to select the range C5:C9 (Where you want to keep the name of the month).
  • Then select the Number Format from Data Tab.

  • Now click on More Number Formats.

convert date to month in excel

  • A new window will appear. Select Custom and type mmmm in the Type menu.
  • Click OK.

  • Now type this formula in cell C5.
=B5

convert date to month in excel

This formula just takes the value from cell B5 and returns the same value but in the way it’s formatted.

  • Press ENTER and you will see the name of the month which carries the date in cell B5.

  • Now, use the Fill Handle to AutoFill the lower cells. You will see the names of the months that the dates contained.

convert date to month in excel

By following this approach, you can convert the dates into corresponding months’ names. 

  • Now if you want to show the months in number, select the cells D5:D9, go to Number Format again.

  • Select More Number Formats…

convert date to month in excel

  • A new window will appear. Select Custom and type mm in the Type menu.
  • Click OK.

  • Now type the following formula in cell D5.
=B5

convert date to month in excel

This formula just takes the value from cell B5 and returns the same value but in the way it’s formatted.

  • Press ENTER and you will see the number of the month which carries the date in cell B5.

  • Use the Fill Handle to AutoFill lower cells.

convert date to month in excel

Thus you can identify the month from the date in cells B5 to B9.

You also can get the month from the date by choosing the date type from the Format Number ribbon.

  • Select the cells B5:B9 and then select More Number Formats. Moreover, you need to select the date format which shows the month. After that, click OK.

After that operation, you will see the name of the months with corresponding dates.

convert date to month in excel

In this way, you can convert date to month within a moment.

Read More: How to Convert Date to Day of Year in Excel (4 Methods)


3. Applying CHOOSE Function to Convert Date to Month

We can apply the CHOOSE function to convert date to month. Let me describe briefly about the process.

Steps:

  • Type the following formula in cell C5. You may type the whole name of these months if you want.
=CHOOSE(MONTH(B5),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Here, the MONTH function returns the position number of a month in the calendar. Here it returns the month number in cell B5 and the CHOOSE function takes this number as an index. It then returns the value according to the index. In this case, it will return Apr because the month number here is 4 so the index number will be 4 and the 4th value in the CHOOSE function is Apr.

  • Now hit the ENTER button and you will get the month name in cell C5.

convert date to month in excel

  • Now use the Fill Handle to AutoFill the lower cells.

Thus you can extract the name of months from dates.

  • To get the month number from these dates, just modify the current formula like below.
=CHOOSE(MONTH(B5),"1","2","3","4","5","6","7","8","9","10","11","12")

convert date to month in excel

Here we just replaced the name of the months with their month number.

  • Now hit the ENTER button and you will see the month number in cell D5.

  • Now use the Fill Handle to AutoFill the lower cells.

convert date to month in excel

By following this path, you can easily convert the dates into their corresponding months.

Read More: Excel Formula for Current Month and Year (3 Examples)


Similar Readings:


4. Utilizing SWITCH Function to Convert Date to Month

Using the SWITCH function can be a valuable key to convert date to month in Excel. Let’s proceed to the execution of this function.

Steps:

  • First, type the following formula in cell C5.
=SWITCH(MONTH(B5),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")

convert date to month in excel

Here the SWITCH function gets the information about the month number from cell B5, then goes through the month list in the code and returns the value of the first match. As the date in cell B5 indicates that the month is April, the MONTH function helps the SWITCH function to return the 4th value of the month list which is April in this case.

  • Now hit the ENTER button and you will see the name of the month in cell C5.

convert date to month in excel

  • Now use the Fill Handle to AutoFill the lower cells.

You can see that the dates in cells B5 to B9 converted to their corresponding month.

  • Now to see the month number, modify the formula a little bit. Type the following formula in cell D5.
=SWITCH(MONTH(B5),1,"1",2,"2",3,"3",4,"4",5,"5",6,"6",7,"7",8,"8",9,"9",10,"10",11,"11",12,"12")

convert date to month in excel

Here we just put the number of the months instead of their names.

  • Hit the ENTER button and you will see the month number of the date in cell B5 in cell D5.

  • Now, use the Fill Handle to AutoFill the lower cells.

convert date to month in excel

Thus you can easily convert date to month in Excel.

Read More: How to Convert Date to Month and Year in Excel (4 Ways)


5. Using TEXT Function to Convert Date to Month

We can convert dates to months simply by using the TEXT function. We can both see the name of the month or the number of the month using this function.

Steps:

  • First, type this simple formula in cell C5.
=TEXT(B5,"mmmm")

Here the TEXT function extracts the month from the date in cell B5. It will show the name of the month as we set the format_text as “mmmm”.

  • Now press the ENTER button. You will see the name of the month of that following date which is April.

convert date to month in excel

  • Now, use the Fill Handle to AutoFill the lower cells. You will see the names of the months that the dates contained.

  • If you want to extract only the number of months from this data, then type the following formula in cell D5.
=TEXT(B5,"mm")

convert date to month in excel

Here the TEXT function extracts the month from the date in cell B5. It will show the number of the month as we set the format_text as “mm”.

  • Now hit the ENTER button and you will see the number of the month of the date in cell B5.

  • Now, use the Fill Handle to AutoFill the lower cells. You will see the number of the months that the dates contained.

convert date to month in excel

Thus you can extract the month from a given date very easily.

Related Content: How to Convert Text to Date with Excel VBA (5 Ways)


6. Using Excel Power Query to Convert Date to Month

Power Query Editor can be a valuable tool to convert date to month in Excel. Let me show you how to use this tool in this prospect.

Steps:

  • Select the cells B4:B9 and then go to Data >> From Range/Table

convert date to month in excel

  • A dialog box will appear. Just click OK.
  • Make sure that My table has headers selected.

  • Eventually, you will see a new window of Power Query Editor containing the Date column. However, we will see a time of 12:00:00 AM by default.

convert date to month in excel

  • Now select the header (Date) and then go to Add Columns >> Date >> Month >> Name of Month

This operation will show you the name of the months of these corresponding dates.

convert date to month in excel

  • To see the month number, go to Add Columns >> Date >> Month >> Month

  • After that, you will see the month number in a new column.

convert date to month in excel

  • Go to the Home Tab of the Power Query Editor and select the Close & Load ribbon. You will get this table in a new Excel sheet.

You will see this table in a new sheet.

convert date to month in excel

This is another easy and efficient method to convert date to month.

Read More: How to Use Formula to Change Date Format in Excel (5 Methods)


Practice Section

I am giving the dataset here so you can practice these methods on your own.

convert date to month in excel_46


Conclusion

In a nutshell, converting dates to months in Excel is a very easy task and it’s also very easy to understand. I hope these interesting methods may benefit you. You can choose any of the methods which suit you the best. If you have other ideas, feedback, or any questions, please feel free to leave them in the comment box.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo