In Excel VBA, use the built-in Month function to get the month number from a date: Month(dateValue) returns a number from 1 to 12. For example, if a date is June 18, 2026, the result is 6; use Format$(dateValue, "mmmm") instead when you want the month name.
Get the month number with Month
Month is the most direct choice when the result should be a number. Pass it a VBA Date value, or a value VBA can interpret as a date, and it returns the calendar month: January is 1, February is 2, and December is 12. It does not return the month’s name, the day, or a month-year combination.
For example, if a variable named orderDate contains June 18, 2026, then Month(orderDate) evaluates to 6. The day and year do not affect the result. A date in June from another year also returns 6.
Here is a minimal procedure that assigns a date and displays its month number:
Sub ShowMonthNumber()
Dim orderDate As Date
Dim monthNumber As Long
orderDate = DateSerial(2026, 6, 18)
monthNumber = Month(orderDate)
MsgBox monthNumber
End Sub
DateSerial constructs a date from year, month, and day arguments. It is useful in examples because it avoids ambiguity between regional date strings such as 06/07/2026, which different settings might read as June 7 or July 6.
How Excel dates affect the result
Excel stores ordinary dates as serial values and displays them according to the cell’s number format. A cell showing “June 18, 2026” may hold a numeric value rather than that text. VBA’s Month function works with the date value, not the way the cell happens to display it. Changing a cell’s display from a long date to a short date does not change its month.
This distinction matters when a cell looks like a date but actually contains text. A true date can be sorted and used in date calculations as a date; text that resembles a date may not behave the same way. Also, a date alone does not identify a particular instant or time zone. If the value includes a time, Month still returns the month portion of that date-time value.
Excel workbooks can use different date systems, and date interpretation can be affected by regional settings. For dependable VBA, pass a real date value from a date cell or construct one with DateSerial, rather than relying on an ambiguous text string.
Read a date from a worksheet cell
To read a date from A2 on a worksheet named Sheet1 and write its month number to B2, first check that the source can be interpreted as a date. The following macro leaves B2 blank and displays a message if A2 is not a usable date.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is important if a procedure changes worksheet contents.
Sub WriteMonthNumber()
Dim ws As Worksheet
Dim rawValue As Variant
Dim dateValue As Date
Set ws = ThisWorkbook.Worksheets("Sheet1")
rawValue = ws.Range("A2").Value
If IsDate(rawValue) Then
dateValue = CDate(rawValue)
ws.Range("B2").Value = Month(dateValue)
Else
ws.Range("B2").ClearContents
MsgBox "A2 does not contain a valid date.", vbExclamation
End If
End Sub
Change the worksheet name and cell references to match your workbook. IsDate checks whether VBA can interpret the value as a date, while CDate converts the accepted value to a Date variable. If your source column contains text dates in mixed regional formats, validation alone may not catch the intended meaning: standardize those values or parse them according to a known format.
Return a month number in a worksheet formula
If you only need a result in a cell and do not need VBA to automate other work, Excel’s worksheet function is simpler. With the date in A2, enter =MONTH(A2) in another cell. For a date of June 18, 2026, it returns 6. This is an Excel formula, not a VBA statement.
In Microsoft 365 for Windows, type the formula into the destination cell and press Enter. The same formula works in current Excel for Mac. Excel for the web can calculate it too, although Excel for the web cannot run VBA macros. If A2 contains text that Excel does not recognize as a date, the formula may return an error rather than the expected month.
Use the formula when the result should update automatically as the source date changes. Use a macro when you need to process many cells, copy results as values, or include month extraction in a larger automated task. A VBA user-defined function is another option if you specifically want a custom function in a worksheet.
Create a VBA function for worksheet cells
To make a reusable VBA function, put this code in a standard module. In the VBA editor, choose Insert > Module, then paste the function. If the workbook is saved with macros, use an Excel Macro-Enabled Workbook (.xlsm) file so the VBA code can be retained.
Function MonthNumber(ByVal inputDate As Date) As Long
' Return the calendar month as a number from 1 to 12.
MonthNumber = Month(inputDate)
End Function
With a valid date in A2, enter =MonthNumber(A2) in a worksheet cell. The result is a number from 1 through 12. The custom function is useful when a workbook already uses VBA functions, but it adds code that must be enabled and maintained. For a straightforward cell calculation, the built-in =MONTH(A2) formula avoids that dependency.
Show a month name instead of a number
For a full month name, use VBA’s Format$ function. For an abbreviated name, use the three-letter format. In both cases, the return value is text, not a number, which affects sorting and calculations.
Sub ShowMonthName()
Dim orderDate As Date
Dim fullMonth As String
Dim shortMonth As String
orderDate = DateSerial(2026, 6, 18)
fullMonth = Format$(orderDate, "mmmm")
shortMonth = Format$(orderDate, "mmm")
MsgBox fullMonth & " (" & shortMonth & ")"
End Sub
The example displays “June (Jun)” in an English-language setting. Month names can vary with regional or language settings, so do not assume that a formatted name will always be English. If a report needs a fixed language, plan for that requirement rather than relying on the computer’s locale. If you need to compare months or group records, keep the month number or the original date as the underlying value and use a name only for presentation.
Month, DatePart, and Format$: which should you use?
Month(dateValue) is the clearest option for a numeric month. VBA’s DatePart can also return the month when asked for the month interval, but it is more general and is often used when extracting other date parts. For this common task, the shorter Month call is easier to read.
Format$(dateValue, "mmmm") is appropriate when you need a month label, while Format$(dateValue, "mmm") gives an abbreviation. These formatted values are strings. For example, the text “10” may sort before “2” in a text-based sort, and a month name alone does not show which year it belongs to. Avoid converting a month number to text unless the output really needs to be text.
When displaying dates directly in worksheet cells, you can also leave the value as a date and change its number format. That preserves the date for sorting and arithmetic. In VBA, formatting a date for display and extracting a month number are different goals: choose the method that matches how the result will be used.
Common problems and reliable fixes
- The result is 1 through 12, not a month name. That is the intended output of
Month. UseFormat$if you need a label such as “June.” - The source looks like a date but fails validation. It may be text, blank, or an unsupported value. Check how the value was imported and convert it to a genuine date before extracting the month.
- The month is off by one or the date is interpreted unexpectedly. Check for ambiguous text such as 04/05/2026 and consider the workbook’s regional settings. Construct dates with
DateSerial(year, month, day)when writing VBA examples or known dates in code. - A month name appears in another language. Formatting can follow locale settings. Keep a numeric month for calculations and use a deliberately controlled label if a report requires a specific language.
- The macro cannot find the worksheet. Confirm that the worksheet tab is named exactly as the code expects. The worksheet name in
Worksheets("Sheet1")is not automatically updated when a differently named sheet is selected.
One common misconception is that changing a cell’s number format changes the date itself. It changes only its display. Another is that month numbers uniquely identify a month in a dataset: month 6 includes June in every year. For monthly summaries across multiple years, group by both year and month, or by a full date such as the first day of each month.
Run VBA in Excel for Windows, Mac, and the web
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic if the Developer tab is visible. To run a selected macro from Excel, use Developer > Macros, choose its name, and select Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
Excel for Mac supports VBA, but keyboard behavior and some VBA features differ from Windows. You can open the editor from the Developer tab; the shortcut may require Option+F11, and on some keyboards Fn may also be needed. If Developer is hidden, use Excel > Settings > Ribbon & Toolbar to show it. Excel for the web does not run VBA; use the desktop Excel application to create or run these macros. Keep a backup and review code before running it, especially if it writes to or clears cells.
Frequently Asked Questions
What VBA function gets the month from a date?
Use Month(dateValue). It returns an integer from 1 to 12, where 1 is January and 12 is December.
How do I get the month name in VBA?
Use Format$(dateValue, "mmmm") for the full name or Format$(dateValue, "mmm") for an abbreviation. The displayed language can depend on regional settings.
Can I use Month on a cell value?
Yes. For example, assign a date cell’s value to a Date variable, then pass that variable to Month. Check text or imported values first, since a value that only looks like a date may not be interpreted as intended.
What is the difference between VBA Month and Excel MONTH?
VBA Month is used in VBA code, while the worksheet formula =MONTH(A2) is entered in a cell. Both return a month number for a valid date.
Can Excel for the web run a VBA macro?
No. Excel for the web can calculate worksheet formulas such as =MONTH(A2), but you need desktop Excel for Windows or Mac to run VBA macros.
Related
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
- Excel Text to Columns: How to Undo and Restore Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Noms Macro Recorder: What It Is and Excel AlternativesIf you mean Microsoft Excel, “Noms macro recorder” is not a standard Excel feature or a recognized Microsoft…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
- Excel VBA Lowercase: Use LCase in Macros and FunctionsIn Excel VBA, use the built-in LCase function to convert text to lowercase; its string-returning counterpart, LCase$, is…
- Create a UserForm in Excel VBA: A Step-by-Step GuideTo create a UserForm in Excel VBA, open the Visual Basic Editor, choose Insert > UserForm, add controls…
- Excel VBA AutoFilter: Filter Rows by a Specific ValueTo filter a range by one exact value with Excel VBA, call AutoFilter on the range that includes…
- Excel VBA InputBox Cancel: Exit a Sub SafelyYes. To stop the current Excel VBA procedure when a user cancels an input box, test the returned…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.