Convert number to month name
How to convert a number, from 1 to 12, into a month's name using an Excel and VBA method
Example: Convert number to month name
|
The formula returns the month's name that is related to the specified number, between 1 and 12. We have manually inserted the first day of a month and any year, in this case using 2017. This is done to convert bring the month's number into a date and then use the Excel TEXT function to convert the date into a month that is applied in the date.
Note: to apply this formula against all of the numbers, as per the image above, you will need to drag (apply) the formula across all of the rows from row 5 to row 16. |
Dim ws As Worksheet
ws.Range("C5") = MonthName(ws.Range("B5"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Number: If using this exact VBA code, which sources the number from cell ("B5"), you will need to capture the month's number in cell ("B5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Number: Select the number which you want to convert to a month name by changing the cell reference ("B5") to any cell in the worksheet that contains the number and doesn't conflict with the formula.
METHOD 2. Convert number to month name with a For Loop
VBA
Dim ws As Worksheet
For x = 5 To 16
ws.Range("C" & x) = MonthName(ws.Range("B" & x))
Next x
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Number Rage: In this example we are converting numbers in range ("B5:B16") into month names. Therefore, if you are using this exact VBA code you will need to capture all the numbers in range ("B5:B16") that you want to convert into a month name.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Number Rage: Select the range that captures the numbers that you want to convert into month names by changing the range reference ("B5:B16") in the VBA code to any range in the worksheet that doesn't conflict with the formula. If you change the number or location of rows then you will need to change the parameters that is driving the For Loop. In this case its the values that we have nominated for x, which are from 5 to 16.
This tutorial provides instructions how to convert a number, from 1 to 12, into a month's name using an Excel and VBA method.
Excel Method: Using an Excel method, we need to apply the Excel TEXT and DATE functions and represent the month number as a date.
VBA Method: Using a VBA method we need to apply the MonthName function and reference the month's number that you want to convert to a month name.
This tutorial provides two VBA methods. The first method converts a single number into a month name. The second method converts a range of numbers into month names.
=TEXT(DATE(year,month,day),"mmmm")
ARGUMENTS
month: The number of the month you want to convert into a month name.
year: Any valid year. In this example we are using year 2017.
day: Any valid day, represented as a numeric value. In this example we are using the first day of the month, represented as 1.