In Excel we can format dates in several ways. We can choose to show just date in short form or date and day together in long date format. We can even choose to display just the day name, day number, get month name or number or the year number.
If Excel user likes to get the month name for a given date, it can be done several ways. Choice of a method ranging from only changing the appearance of date to getting the results dynamically depends on how we like to process the formatted result.
In this tutorial:
In this tutorial we are learning five different ways to return a month name from a given date in Excel.
Get Month name from date with Custom Formatting
The easiest of all the ways in Excel to get a month name is by using Custom Formatting. Hardly takes a minute to do it and the best thing about this approach is that it only changes the appearance of the date i.e. how the date is displayed. But at the backend it is still a number and can be used just like a regular date value in Excel formulas.
Following is the example date dataset and the steps to convert dates to only month name:
1. Select the data range containing dates using CTRL+A shortcut or with mouse.
2. Go to Home tab > under number group > click the dialogue box button at the bottom right corner of the group. Or you can hit CTRL+1 on the keyboard to invoke format cell dialogue box.
3. From the list on the left in the dialogue box click Custom. This will let us input custom format code as per our requirements.
4. In the type input field remove anything existing and put “mmmm” as format code to get the complete month name. You can also preview the results just above the input bar to confirm.
5. Click OK and you will have the month names.
6. If you want the month names from each date in short form then you can input “mmm” as the format code and Excel will return short month names of the respective date values.
Format Date to Month Name with TEXT Function
As Custom Formatting option only changes how cell contents are displayed without changing underlying data, it often defeats the purpose.
Suppose we like to get a month name from date data and attach the task name for that month or the week number of the year. This is not possible with simple custom formatting code.
For this, we have Excel TEXT function that formats exactly as custom formatting but allows us to use the results in a more flexible way.
Continuing with the same example as before, to get the month names in column B from the date values in column A, put the following formula in cell B2:
=TEXT(A2,”mmmm”)
Now if we want to show the week number following the month name for any date value, we can use the following formula:
=TEXT(A2,"mmmm")&" - W"&WEEKNUM(A2)
Similarly, if we like to have a short month name with the week number, we will use “mmm” as the format code just like we did in the custom formatting above. And the formula is:
=TEXT(A2,"mmm")&" - W"&WEEKNUM(A2)
Using SWITCH + MONTH Function to convert date to Month Name
Moving away from “format only” approach, we can use the combination of SWITCH and MONTH functions in Excel.
MONTH function provides us with month number for a given date. With month number available, we can use SWITCH function to switch number value with the month name.
Continuing with the same example, we can use the following formula to get the month name from dates:
=SWITCH(A2,1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec")Here is the formula again with line breaks:
=SWITCH(MONTH(A2),
1,"Jan",
2,"Feb",
3,"Mar",
4,"Apr",
5,"May",
6,"Jun",
7,"Jul",
8,"Aug",
9,"Sep",
10,"Oct",
11,"Nov",
12,"Dec")Again, MONTH function is returning a month number for the date mentioned in cell A2. And SWITCH function is replacing the number with the relevant “month name” provided in the formula.
Here are the results:
The good thing about this approach is that you are not limited to standard English month names. You can use your local month names as well which is not possible with most of the approaches discussed in this tutorial.
Like if we want Turkish month names then the formula be:
=SWITCH(MONTH(A2),1,"ocak",2,"şubat",3,"mart",4,"nisan",5,"mayıs",6,"haziran",7,"temmuz",8,"ağustos",9,"eylül",10,"ekim",11,"kasım",12,"aralık")Here are the results:
Excel CHOOSE function to get month names
CHOOSE function works exactly the way the SWITCH function works and is also a little simpler to write.
Like the SWITCH function, CHOOSE function will choose a particular name based on the number given by the MONTH function.
For our example, here is the formula:
=CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")If, the MONTH(A2) function returns 5, CHOOSE function will return “May” as the result. Similar if MONTH function returns 1, CHOOSE function will give “Jan” as the result.
Here is the result of our example:
Power Query to get Month name from Date
Among all the methods discussed above, Power Query is probably the most effective way to handle this problem. Especially if your data grows and updates on regular basis.
With the above approaches, we will have to readjust data and the application of the formula in some situations. With Power Query, however, this is not a problem. And we can even modify the processing in a much easier manner.
Starting with our example, select the data and convert it to table range by hitting CTRL+T on the keyboard. Alternatively, you can go to Insert tab > click Table.
With an active cell inside the tabular range > go to data tab > under get & transform data group, click From Table/Range. This will open Power Query window.
Left-click the header of the column that contains dates. Go to transform tab > click Date drop-down button > hover over month > click Name of month. This will instantly convert dates to month names.
Go to Home tab > click Close and Load To… drop-down > select new worksheet if you want data in the new worksheet. Or specify the cell address if you want the data to be inserted within the same worksheet. I went ahead with the same worksheet.
Now even if data in the original table changes, or more rows are added to it, power query will automatically update.
So we learnt some of the best methods to get a month name from a date in Excel.
I hope you this tutorial guide helpful in learning new Excel tricks.
Checkout more Excel tutorial:
