Returns the month corresponding to a date. Extracts the month component (1-12) from a date serial number, essential for date analysis, seasonal reporting, and period-based calculations.
Master the fundamentals of Excel MONTH function
The MONTH function extracts the month component from a date serial number, returning a value between 1 and 12. This function is fundamental for date analysis, enabling seasonal analysis, period-based calculations, and date component manipulation. MONTH works with Excel's date serial number system where dates are stored as numbers.
Extracts the month component (1-12) from any valid date, where 1=January and 12=December.
Always returns a number between 1 and 12, regardless of input date, providing predictable output for calculations.
Works with Excel's date serial number system, accepting dates in various formats including serial numbers, date strings, and cell references.
Returns #VALUE! for invalid dates, ensuring data integrity in calculations and preventing silent errors.
Function-specific parameters
Function-specific return type
Analyze data by seasons using month values for business intelligence
Calculate fiscal periods, quarters, and reporting periods from dates
Validate dates and check date ranges based on month values
Group financial data by months for periodic reporting and analysis
Exact matching required
Returns numeric position
Handles missing text gracefully
=MONTH(serial_number)The date for which you want to find the month. Can be a date serial number, date string, or cell reference containing a date.
The month of the year as a number (1-12, where 1=January, 12=December)
Description: Extracts the month component from a date
Extract month from a specific date
Returns 1 for January. MONTH extracts the month component from the date serial number.
Using MONTH function in VBA
Sub MONTHExample()
Dim result As Integer
Dim myDate As Date
myDate = DateValue("2024-01-15")
result = Month(myDate)
Range("A1").Value = result
MsgBox "MONTH(2024-01-15) = " & result
End Sub
' Extract month from cell
Sub ExtractMonthFromCell()
Dim cellValue As Date
Dim monthValue As Integer
cellValue = Range("A1").Value
monthValue = Month(cellValue)
Range("B1").Value = monthValue
End Sub
' Loop through dates and extract months
Sub ExtractMonthsFromRange()
Dim cell As Range
Dim monthVal As Integer
For Each cell In Range("A1:A10")
If IsDate(cell.Value) Then
monthVal = Month(cell.Value)
cell.Offset(0, 1).Value = monthVal
End If
Next cell
End SubAnalyze data by seasons using month values
Calculate fiscal quarter from date
Group data by month for reporting
Validate dates fall within specific months
MONTH returns #VALUE! error
=MONTH("invalid date")Solution: Ensure the argument is a valid date. Check that the cell contains a date serial number, not text. Use DATEVALUE() to convert text dates, or verify cell formatting is set to Date.
MONTH returns unexpected values
=MONTH(A1)Solution: Verify the input is actually a date. Text that looks like dates may be interpreted incorrectly. Ensure cells are formatted as dates and contain valid date values. Use ISDATE() to verify before using MONTH.
Want month name instead of number
=MONTH(A1)Solution: Use TEXT function with MONTH: =TEXT(DATE(2024, MONTH(A1), 1), "mmmm") to get month name. Or use CHOOSE: =CHOOSE(MONTH(A1), "January", "February", ...) for custom month names.