NOTE: Other languages are Google-Translated. You can go to the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

How to convert month name to number in Excel?

Sometimes, you may want to convert the month name to number or the number to month name, such as doc-convert-month-name-to-number-1 , in Excel, you can use formulas and VBA to quickly convert between month name and number.

Convert month name to number in Excel

Convert number to month name with VBA

Convert date to month name or month number with Kutools for Excel good idea3

Quickly and easily convert date to other data formatting in Excel

Have you ever tried to convert a date to day, month or year only? The formulas maybe hard to remember, but the Apply Date Formatting of Kutools for Excel can quickly convert a standard date to the date formatting as you need as below screenshot shown. Dont, waite, click for 60 days free trial!
doc apply date format
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

arrow blue right bubble Convert month name to number in Excel


There are two ways that can help you to convert month names to numbers in Excel.

Method 1: Convert month name to number with formula.

Type this formula =MONTH(DATEVALUE(A1&" 1")) ( A1 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:

If you want to convert a column list of month names to numbers, just drag the fill handle of the formula cell to fill the range you need. See screenshot:

Method 2: Convert month name to number with VBA

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert month name to number

Sub ChangeNum()
'Updateby20140311
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Value <> "" Then
        Rng.Value = Month(DateValue("03/" & Rng.Value & "/2014"))
    End If
Next
End Sub

3. Click Run to run the VBA code, and a KutoolsforExcel dialog pops up for you to select a range of cells with month names that you want to convert to numbers. See screenshot:

4. Click OK, the month names in the range have been converted to numbers. See screenshot:

Tip: Using the above VBA may lose your original data, you can save them before you running the VBA code.


arrow blue right bubble Convert number to month name with VBA

In reverse, if you want to convert numbers to month names, you also can use two methods to solve it.

Method 1: Convert number to month name with formula.

Type this formula =TEXT(DATE(2000,A1,1),"mmmm") ( A1 indicates the cell that you want to convert the number to month name, you can change it as you need) into a blank cell, and press Enter key. See screenshot:

If you want to convert a column list of numbers to month names, just drag the fill handle of the formula cell to fill the range you need. See screenshot:

Tip: If you want to convert number to the abbreviation of the month name, you can use this formula =TEXT(DATE(2000,A1,1),"mmm").

Method 2: Convert number to month name with VBA

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert number to month name

Sub ChangeMonth()
'Updateby20140311
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.Format(Rng.Value * 29, "mmmm")
Next
End Sub

3. Click Run to run the VBA code, and a KutoolsforExcel dialog pops up for you to select a range of cells with numbers you want to convert to the month names. See screenshot:

4. Click OK, the selected numbers in the range have been converted to month names. See screenshot:

Tip:

1. Using the above VBA may lose your original data, you can save them before you running the VBA code.

2. If you want to convert number to the abbreviation of the month name, you can change "mmmm" to "mmm" in the above VBA.


arrow blue right bubble Convert date to month name or month number with Kutools for Excel

If you have a list of dates in a worksheet needed to convert to month name or month number, in this case, you can apply Kutools for Excel’s Apply Date Formatting utility.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, just do as below:

1. Select the dates and click Kutools > Format > Apply Date Formatting. See screenshot:doc date to month kte 1

2. Then in the popped out dialog, select the date format you need from the Date formatting list, and you can see the result from the Preview pane.doc date to month kte 2

3. Then click Ok or Apply, you can see the dates have been converted to relative month names or month numbers.

Date to month number
Date to month name
doc date to month kte 3
doc date to month kte 4

Click here to know more about Apply Date Formatting

arrow blue right bubble Convert date to month number or month name or other date formats

 

Quickly convert nonstandard date to standard date formattiing(mm/dd/yyyy)

In some times, you may received a workhseets with multiple nonstandard dates, and to convert all of them to the standard date formatting as mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert these nonstandard dates to the standard date formatting with one click.  Click for 60 days free trial!
doc convert date
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    M BAMAGA · 11 months ago
    wf = one week from now
    mf= one month from the entry
    2mf= 2 months from the entry
    4mf= 4 months from the entry
    6mf= 6 months from the entry
    yf= one year from the entry
    I am wondering if possible to covert this entry in another column as due dates.
  • To post as a guest, your comment is unpublished.
    Nihar Panda · 2 years ago
    If you pre-filling the month names, did you try using a vlookup instead of using a VBA?
  • To post as a guest, your comment is unpublished.
    maharba · 2 years ago
    Thank you It works in my case
  • To post as a guest, your comment is unpublished.
    Rakesh Sharma · 3 years ago
    This is funny. Following Function converts the Filename which is Month Name to its corresponding Numerical value


    =MONTH(1&LEFT((MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))),3))
  • To post as a guest, your comment is unpublished.
    curious · 3 years ago
    Excel function MONTH: does it get the number from text for example from NOV it would get 11, or: the text from the number for example from 11 it would get NOV, or what? On my location it goes from 11 to 11, not very interesting!!