In Excel VBA, the Month function returns the month number from a date: an integer from 1 for January through 12 for December. Use it as Month(date), where date is a date value or an expression VBA can interpret as a date.
What the VBA Month function returns
Month extracts the month component of a date; it does not return a month name, count elapsed months, or change the original date. For example, applying it to April 18, 2025, produces 4. The returned number is always in the range 1 to 12 for a valid date, regardless of the day or year.
This is a built-in VBA function, so it does not need to be enabled or declared. In code, call it directly. Its result is useful when a macro needs to group records by month, test a date, build a report, or choose a month-specific action. Because the output is a number, it is convenient for comparisons and calculations but may need formatting if you want to show a word such as “April.”
Syntax and accepted date values
The syntax is Month(date). The argument can be a VBA Date variable, a date returned by a function such as Date or Now, or a value read from a worksheet cell. VBA must be able to interpret the supplied value as a date. If it cannot, the code may raise a type mismatch error rather than return a month number.
For predictable dates in code, use DateSerial(year, month, day) rather than writing a date as text. DateSerial constructs a date from numeric parts and avoids relying on the computer’s regional interpretation of strings such as “04/05/2025,” which can mean April 5 or May 4. A useful minimal example is:
Sub ShowMonthNumber()
Dim sampleDate As Date
Dim monthNumber As Long
' Build an unambiguous date and extract its month.
sampleDate = DateSerial(2025, 4, 18)
monthNumber = Month(sampleDate)
MsgBox monthNumber
End Sub
When this macro runs, the message box displays 4. The variables are declared explicitly: sampleDate holds a date, and monthNumber holds the integer result. If the input comes from a cell that may contain text, blanks, or errors, validate it before calling Month.
Using Month with dates in worksheet cells
A common task is to read a date from one column and place its month number in another. The following example checks each populated row in column A, starting at row 2, and writes the month number to column B. If a cell is not recognized as a date, it writes a blank instead of trying to extract a month.
Sub WriteMonthNumbers()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNumber As Long
Dim cellValue As Variant
' Read dates from column A and write month numbers to column B.
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For rowNumber = 2 To lastRow
cellValue = ws.Cells(rowNumber, "A").Value
If IsDate(cellValue) Then
ws.Cells(rowNumber, "B").Value = Month(CDate(cellValue))
Else
ws.Cells(rowNumber, "B").ClearContents
End If
Next rowNumber
End Sub
Change Sheet1 to the actual worksheet name. The example assumes row 1 contains headings and dates begin in row 2. IsDate tests whether the value can be interpreted as a date, while CDate converts it to VBA’s Date type before extraction. A text date can still be interpreted differently depending on regional settings, so genuine Excel date values are preferable when a workbook is shared across computers.
The code uses Range.Value, which normally returns a worksheet date as a VBA date value when the cell contains a date. Be more cautious with Value2: it returns the underlying value without converting dates to the VBA Date type. If you read a date serial using Value2, account for the workbook’s date system and convert it appropriately rather than assuming the number is already a VBA date.
Returning a month name instead of a number
Month returns a number, not a label. To get a month name from a date in VBA, use MonthName with the result of Month. Its optional second argument controls whether the name is abbreviated:
Sub ShowMonthName()
Dim sampleDate As Date
Dim monthNumber As Long
Dim monthLabel As String
' Request the full month name for the sample date.
sampleDate = DateSerial(2025, 4, 18)
monthNumber = Month(sampleDate)
monthLabel = MonthName(monthNumber, False)
MsgBox monthLabel
End Sub
This displays “April” in an English-language environment. Setting the second argument to True requests an abbreviated name, such as “Apr.” The displayed language can depend on the system’s locale, so do not assume that MonthName will always produce an English label on every computer. For a value intended for display in a cell, you can also write the date itself and apply a date number format rather than replacing it with text.
Common uses in macros and reports
Extracting a month is especially useful when a procedure needs to compare dates by calendar month. For example, a macro can test whether Month(someDate) = 12 to identify dates in December. However, the month number alone does not identify a unique month across multiple years: January 2024 and January 2025 both return 1. If the year matters, compare it as well, or compare a complete date range.
For example, a report that groups sales by month and year should keep both parts of the period, or use a true first-of-month date as its grouping key. Storing only the result of Month would merge January records from every year. The function also does not sort data or group rows automatically; it only supplies the month component for the macro’s next step.
For a date range, a robust approach is often to compare each date against the start and end of a month rather than checking only its month number. That makes the year explicit and avoids mixing records from different years. You can construct boundaries with DateSerial; for example, it can create the first day of a chosen month. When you use month arithmetic, remember that DateSerial normalizes month values outside 1 through 12, which can be useful for calculating the next year but should be understood rather than mistaken for a validation error.
Month in VBA compared with Excel MONTH
Excel’s worksheet function MONTH and VBA’s Month have the same basic purpose: both extract a month number from a date. The difference is where and how you use them. In a worksheet cell, a formula can calculate the month for that row; inside a VBA procedure, call Month on a variable or expression. The VBA function name is not a worksheet formula to type directly into a cell.
For example, with an Excel date in A2, the worksheet formula =MONTH(A2) returns the month number for that cell. If A2 contains April 18, 2025 as a real date, the result is 4. In a macro, Month(Range("A2").Value) can retrieve the equivalent number in code, though assigning the cell value to a declared variable and checking it first is safer when the cell may be empty or invalid.
Choose the worksheet function when the result should remain visible and recalculate with the sheet. Choose VBA when the month is part of a larger automated task, such as moving records, creating a monthly file, or filling report output. A macro is not required merely to display a month number beside a date.
Common errors and misconceptions
- Expecting a name:
Month(date)returns a number. UseMonthNamewhen a text label is needed. - Passing a non-date: A blank, arbitrary text, or an Excel error value may not be convertible. Validate worksheet input and handle errors when the data source is uncertain.
- Confusing a number with a month: The result
4represents April; it is not a date serial for the fourth day of a month. - Ignoring the year: Comparing only month numbers combines the same month across different years. Include the year or compare full date boundaries.
- Assuming text dates are universal: Text such as “03/04/2025” is ambiguous across regional settings. Store dates as actual Excel dates or construct known dates with
DateSerial.
A related misconception is that Month changes a date or rounds it to the start of a month. It does neither: the input remains unchanged, and the function simply returns one component. If a macro needs a month-start date, it must construct one separately, for example with DateSerial(Year(someDate), Month(someDate), 1).
Where to put the VBA code and how to run it
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, use Insert > Module and place a complete procedure in the standard module. Return to Excel and run a macro from the editor, or use Developer > Macros to select and run it. The Developer tab may need to be enabled through Excel’s ribbon settings if it is not visible.
Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA code. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros; use the desktop Excel application for those tasks. Excel for Mac also includes VBA support, although available keyboard shortcuts and some editor details can differ from Windows. Before running a macro that writes to a sheet, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.
Frequently Asked Questions
What does Month return in VBA?Month returns an integer from 1 to 12 for a valid date. January is 1, February is 2, and December is 12.
How do I get the current month in Excel VBA?
Call Month(Date) to get the month number for the computer’s current date. Use Month(Now) if you have a reason to start with the current date and time; the month component is the same.
How do I display April instead of 4?
Use MonthName(Month(someDate)) to request the full month name, or pass True as the second argument to request an abbreviation. The label’s language can follow the system locale.
Why does Month give an error for a cell?
The cell may be blank, contain text that is not a recognizable date, or contain an Excel error. Check the value with IsDate before converting it with CDate and calling Month. Text dates can also be interpreted differently under different regional settings.
Does Month distinguish January in different years?
No. January in any year returns 1. Include Year(date) as well, or compare the original date against a range for the specific month and year you intend to process.
Related
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Sum: Methods, Examples, and Common ErrorsTo sum a range in Excel VBA, use Application.WorksheetFunction.Sum and assign its result to a variable or cell.…
- Excel VBA Send Email in New Outlook: What Actually WorksExcel VBA cannot directly automate the new Outlook for Windows: the new app does not provide the classic…
- Excel UserForm Option Button: Setup and VBA GuideAn Excel UserForm option button (also called a radio button) lets a user choose one item from a…
- Excel VBA Date Subtraction: Get the Number of DaysIn Excel VBA, subtract one date from another with days = endDate - startDate; for dates without times,…
- Call a Module from a UserForm Command Button in ExcelTo call a VBA procedure in a standard module from an Excel UserForm command button, put the button’s…
- Excel UserForm Scrollbar: Setup, Events, and ExamplesTo use a scrollbar on an Excel UserForm, add a ScrollBar control in the Visual Basic Editor, set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.