Excel VBA Get Month From Date: Code, Formats, and Tips

VBA & macros · Updated November 21, 2026

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. Use Format$ 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.