How to Change and Display Month Number as Name Text in Excel

Microsoft Excel has several functions that allows manipulation of date and time, and one of them is MONTH(), which returns the month of a date in number ranging from 1 (for January) to 12 (December). Some Microsoft Office Excel users may prefer the month to be shown or displayed as month name in text instead of number, in the form of January, February, March and so on, or simply Jan, Feb, Mac and etc, instead of 1, 2, 3…

In order to convert the number to month in text format to show the name of month, use the following function in the cell which the name of month should be displayed;

=TEXT(DATE(2010, A1, 1), “mmmm”)

Where 2010 is a dummy year, A1 is the cell number where the month number is located, and 1 is dummy day.

If the cells contain a date instead (and not the month number), it’s possible to use the MONTH() function to retrieve the month number. The dummy year and day can also be replaced with YEAR() and DAY() functions to supply year and day number of the date, which may be useful for manipulation later. For example,

=TEXT(DATE(YEAR(A1), MONTH(A1), DAY(A1)), “mmmm”)

Where A1 cell contains the date.

The format text of TEXT() function can be re-defined to return the month name (and even year, day or date) in prefer format. Use the following format_text (to replace “mmmm”) for the different display of month name in text:

“mmmmm” = Only first letter of the month is shown
“mmmm” = Month is spelled out completely
“mmm” = 3 letter abbreviation of month (i.e. Jan, Feb, Mac…)

Or, it can be mixed with year and day format text freely to get various date format:

“yy” = Last two digits of year without century
“yyyy” = Full four digits year format
“d” = Single digit when showing 1 – 9 day in calendar
“dd” = Two digit day display with 1 – 9 day in calendar appended with 0 in front