How to add month name from date in Excel formula

Excel has lately gained popularity as it enables the users to organize data in meaningful categories and due to its advance features. This article holds a collection of formulas and methods to get month names and its goal is to display month name from any given data. Sometimes, when working with date-related data, you may want to add month name in Excel to number or the number to month name, such as convert January to 1, in Excel, you can use formulas to quickly convert between month name and number.There are various methods to add month names in excel some of which are listed below:

Extracting month name from date in excel:

Follow these steps to add month name in Excel:

In case you want to get a month name rather than a number, you use the TEXT function in Excel.

1. =TEXT(A2, mmm)- shifts an abbreviated month name, as Jan - Dec

2. =TEXT(A2,mmmm)- returns a full month name, as January - December.

You don’t need any formulae if you don’t want to convert date to month in your spreadsheet, rather you wish to only display month name instead of full date.

1. Select the cell in which you have written all the dates

2. Then press Ctrl+1 so that the format cells dialog will appear.

3. Next, click number and then select customs and type either”mmm”or “mmmm” to get shortened or complete name respectively. In this case, your entries will be able to get used in further computations and activities.

How to convert month number to month name in Excel:

Follow these steps to add month name in Excel:

If  you have a list of numbers (1 till 12) in your Spreadsheet that you want to convert to month names. To do this, you can use any of the following formulas:

To return an abbreviated month name (Jan - Dec), you can use the method listed below:

=TEXT(A2*28, mmm)

=TEXT(DATE(2015, A2, 1), mmm)

To return a full month name (January - December), you may use the method listed below:

=TEXT(A2*28, mmmm)

=TEXT(DATE(2015, A2, 1), mmmm)

In above formulas, A2 is a cell with a month number. And the only real difference between the formulas is the month codes:

1. mmm - shortened name of the month, such as Aug - Dec

2. mmmm - complete month name

3. mmmmm - first letter of the month name

When used in combination with a month format code such as mmm or mmmm, Excel considers the number 1 to be the first day of January 1900. Multiply 1, 2, 3, etc. by 28 to get the 28^th, 56^th and 84^th day of year 1900 which are in January, February, March, etc. The mmm or mmmm format code displays only the name of the month.

Converting month name to number in excel:

Follow these steps to add month name in Excel:

There are two Excel functions that can help you convert month names to numbers - DATEVALUE and MONTH. Excel's DATEVALUE function converts a date stored as text to a serial number that Microsoft Excel recognizes as a date. And then, the MONTH function extracts a month number from that date.

The complete formula is as follows:

=MONTH(DATEVALUE(D1 “1”))

Type this formula =MONTH(DATEVALUE(D1& 1)) ( D1 indicates the cell that you want to convert the month name to number, you can change it as you need) into a blank cell, and press Enter key. See screenshot attached below:

Did you learn about how to add month name in Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience