# you must type all 12 month abbreviations to fill a range of cells with jan, feb, ..., dec

### Mohammed

Guys, does anyone know the answer?

get you must type all 12 month abbreviations to fill a range of cells with jan, feb, ..., dec from screen.

## 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:

## How to add month name from date in Excel formula

11.6K Views 0 4

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.

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

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

Select the cell in which you have written all the dates

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

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:

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

mmmm - complete month name

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 28th, 56th and 84th 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

Trending Tutorials

1. How to compare two excel sheets and highlight differences

2. How to expand cells to fit text in Excel

3. How to import external data in WPS Spreadsheet

4. How to copy file names in Excel from a folder?

5. How to use the VLOOKUP function across multiple sheets in WPS Office Excel?

6. How to insert a check box in WPS Spreadsheet

### Recommended

Line Charts and Area Charts

718 09:37

How to make a simple lottery through the RANDBETWEEN Function?

1.1K 02:02

How to use the VLOOKUP function

1.8K 04:29

### Popular Searches of Office Suite

how to white out text in pdf

wps publisher free download

libreoffice download chromebook

make a histogram in excel

how to download resume on iphone

## Excel MONTH function

The tutorial explains Excel MONTH and EOMONTH functions in full detail. Formula examples show how to extract month from date in Excel, get the first and last day of month, convert month name to number and more.

## Using MONTH and EOMONTH functions in Excel - formula examples

by Svetlana Cheusheva, updated on November 24, 2022

The tutorial explains the nuts and bolts of Excel MONTH and EOMONTH functions. You will find an array of formula examples demonstrating how to extract month from date in Excel, get the first and last day of the month, convert month name to number and more.

In the previous article, we explored a variety of formulas to calculate weekdays. Today, we are going to operate on a bigger time unit and learn the functions that Microsoft Excel provides for months.

In this tutorial, you will learn:

Excel MONTH function - syntax and uses

Get month number from date in Excel

Extract month name from date

Convert number to month name

Convert month name to number

Get the last day of month (EOMONTH function)

Find the first day of month

Calculate the number of days in a month

How to sum data by month in Excel

How to conditionally format dates based on month

## Excel MONTH function - syntax and uses

Microsoft Excel provides a special MONTH function to extract a month from date, which returns the month number ranging from 1 (January) to 12 (December).

The MONTH function can be used in all versions of Excel 2016 - 2000 and its syntax is as simple as it can possibly be:

MONTH(serial_number)

Where serial_number is any valid date of the month you are trying to find.

For the correct work of Excel MONTH formulas, a date should be entered by using the DATE(year, month, day) function. For example, the formula =MONTH(DATE(2015,3,1)) returns 3 since DATE represents the 1st day of March, 2015.

Formulas like =MONTH("1-Mar-2015") also work fine, though problems may occur in more complex scenarios if dates are entered as text.

In practice, instead of specifying a date within the MONTH function, it's more convenient to refer to a cell with a date or supply a date returned by some other function. For example:

=MONTH(A1) - returns the month of a date in cell A1.

=MONTH(TODAY()) - returns the number of the current month.

At first sight, the Excel MONTH function may look plain. But look through the below examples and you will be amazed to know how many useful things it can actually do.

## How to get month number from date in Excel

There are several ways to get month from date in Excel. Which one to choose depends on exactly what result you are trying to achieve.

**MONTH function in Excel - get month number from date**

This is the most obvious and easiest way to convert date to month in Excel. For example:

=MONTH(A2) - returns the month of a date in cell A2.

=MONTH(DATE(2015,4,15)) - returns 4 corresponding to April.

=MONTH("15-Apr-2015") - obviously, returns number 4 too.

**TEXT function in Excel - extract month as a text string**

An alternative way to get a month number from an Excel date is using the TEXT function:

=TEXT(A2, "m") - returns a month number without a leading zero, as 1 - 12.

=TEXT(A2,"mm") - returns a month number with a leading zero, as 01 - 12.

Please be very careful when using TEXT formulas, because they always return month numbers as text strings. So, if you plan to perform some further calculations or use the returned numbers in other formulas, you'd better stick with the Excel MONTH function.

The following screenshot demonstrates the results returned by all of the above formulas. Please notice the right alignment of numbers returned by the MONTH function (cells C2 and C3) as opposed to left-aligned text values returned by the TEXT functions (cells C4 and C5).

## How to extract month name from date in Excel

In case you want to get a month name rather than a number, you use the TEXT function again, but with a different date code:

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

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

If you don't actually want to convert date to month in your Excel worksheet, you are just wish to **display a month name** only instead of the full date, then you don't want any formulas.

Select a cell(s) with dates, press Ctrl+1 to opent the Format Cells dialog. On the Number tab, select **Custom** and type either "mmm" or "mmmm" in the **Type** box to display abbreviated or full month names, respectively. In this case, your entries will remain fully functional Excel dates that you can use in calculations and other formulas. For more details about changing the date format, please see Creating a custom date format in Excel.

## How to convert month number to month name in Excel

Suppose, you have a list of numbers (1 through 12) in your Excel worksheet 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):**

=TEXT(A2*28, "mmm")

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

**To return a full month name (January - December):**

=TEXT(A2*28, "mmmm")

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

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

## Converting name of month to number

Converting name of month to number. Learn more about month, number

## Converting name of month to number

77 views (last 30 days)

Show older comments

Adrij Roy on 28 Jan 2019

**⋮**

Edited: Adam Danz on 9 May 2021

Accepted Answer: Walter Roberson

I have a cellmatrix where there are two columns wth name of months. I want to replace the name of months by serial numbers from 1 to 12.

How can I do it in matlab 2016a?I am new to matlab & I tried using strcmp, strrep & even with switch.

Please suggest. 1)

if strcmp(z1(n,5),month(m,1))

z1{n,5} = strrep(z1{n,5},'z1{n,5}','m')

break;

else z1{n,5} = z1{n,5};

end 2) str = z1{n,5}; switch (str) case ('January') z1{n,5} = 1; . . . case ('December') z1{n,5} = 12; end

None worked correctly.

5 Comments

Show 4 older comments

Sarah Crimi on 1 Feb 2019

**⋮**

Oh yes, you are right!

Sign in to comment.

Sign in to answer this question.

### Accepted Answer

Walter Roberson on 28 Jan 2019

**⋮**

[tf, idx] = ismember(z1(:,5), {'January', 'February', 'March'})

now tf(nn) is true if z1{nn,5} is matched and idx(nn) is the month number if tf(nn) is true. No loop needed.

2 Comments

Show 1 older comment

Adrij Roy on 29 Jan 2019

**⋮**

Sir I did with strcmp. The month names had a space at last so characters were not maching earlier.

Sign in to comment.

### More Answers (1)

Adam Danz on 9 May 2021

**⋮**

Edited: Adam Danz on 9 May 2021

Ran in:

Another way to convert month names to numbers that is quite flexible in ignoring case and accpeting month appreviations. Requires Finance Toolbox

monthNames = {'jan','March','october','Nov'}; % accepts string arrays, too

monthNum = month(monthNames,"mmmm")

monthNum = 1×4 1 3 10 11

Another option that does not require any toolbox but is not as quite as flexible since appreviated month names require a different format string.

% Full month names, not case senstive

monthNames = {'March','May','june'};

month(datetime(monthNames,'InputFormat','MMMM')) % 4 M's

ans = 1×3 3 5 6

% Abbreviated month names (3 letters), not case sensitive

monthNamesAbrv = {'Jan','Oct','dec'};

month(datetime(monthNamesAbrv,'InputFormat','MMM')) % 3 M's

ans = 1×3 1 10 12

A safer version of the example above in cases where abbreviations are longer than 3 letters (ie, "Sept")

monthNamesAbrv = {'sept','oct','June'};

monthNamesAbrvClean = cellfun(@(str){str(1:3)},cellstr(monthNamesAbrv));

month(datetime(monthNamesAbrvClean,'InputFormat','MMM')) % 3 M's

ans = 1×3 9 10 6 2 Comments

Show 1 older comment

Adam Danz on 9 May 2021

**⋮**

Thanks WR. I often overlook dependencies for some toolbox functions I bump into without ever looking them up. I'll update my answer because I just found another way worth sharing, too.

Sign in to comment.

Sign in to answer this question.

Guys, does anyone know the answer?