Master the MONTH Function: Extract Month Numbers from Dates in Excel
=MONTH(serial_number)The MONTH function is one of Excel's most essential date manipulation tools, designed to extract the month component from any date value and return it as a number between 1 and 12. Whether you're managing project timelines, analyzing sales data by month, or organizing financial records, the MONTH function provides a straightforward way to isolate the month portion of a date without complex calculations. This beginner-friendly formula works consistently across all modern Excel versions and integrates seamlessly with other date functions to create powerful analytical solutions. Understanding how to use the MONTH function effectively transforms how you work with date data in Excel. Rather than manually parsing dates or using complicated text formulas, MONTH delivers precise results instantly. It's particularly valuable when you need to group data by month, create monthly reports, calculate age in months, or build dynamic dashboards that respond to seasonal changes. Learning this function opens doors to more advanced date manipulation techniques and significantly improves your data analysis capabilities.
Syntax & Parameters
The MONTH function uses a simple syntax structure: =MONTH(serial_number). The serial_number parameter represents the date from which you want to extract the month value. This parameter accepts several input formats: direct date values like DATE(2024,3,15), cell references containing dates (A1), or text strings formatted as recognizable dates ("2024-03-15"). Excel internally stores dates as serial numbers, where January 1, 1900 equals 1, and each subsequent day increments the value by one. The MONTH function converts this serial representation into a human-readable month number. When you provide a date to the MONTH function, Excel automatically recognizes it and extracts the month component, returning values from 1 (January) through 12 (December). The function ignores time components entirely, so =MONTH("2024-03-15 14:30:00") returns 3 without any decimal values. If you reference a cell containing a date, ensure the cell is formatted as a date rather than text, or the function may return #VALUE! error. The serial_number parameter is mandatory; omitting it or providing invalid data causes errors. For best results, use the DATE function to create explicit date values or reference cells with proper date formatting.
serial_numberPractical Examples
Extracting Month from Current Date
=MONTH(TODAY())This formula combines TODAY() to get the current date and MONTH() to extract just the month number. If today is March 15, 2024, the formula returns 3. This is useful for creating dynamic reports that automatically reference the current month without manual updates.
Extracting Month from Invoice Date
=MONTH(A2)Cell A2 contains the date "2024-03-22". The MONTH function extracts the month component, returning 3. This can be copied down the entire column to quickly categorize all invoices by month, enabling pivot table analysis or SUMIF formulas that group by month.
Calculating Months Between Dates
=(YEAR(TODAY())-YEAR(A3))*12+MONTH(TODAY())-MONTH(A3)This advanced combination uses MONTH with YEAR functions to calculate precise months of employment. If hired on January 15, 2022, and today is March 15, 2024, the formula calculates (2024-2022)*12 + (3-1) = 26 months. This demonstrates how MONTH combines with other date functions for complex calculations.
Key Takeaways
- MONTH extracts the month number (1-12) from any date value, providing a simple way to categorize and analyze data by month.
- The function works with direct dates, cell references, and DATE function results, but requires valid date values to avoid #VALUE! errors.
- MONTH combines effectively with other functions like YEAR, SUMIF, and TEXT to create powerful date analysis and reporting solutions.
- Always ensure dates are formatted as Date type rather than Text to guarantee MONTH function reliability across all scenarios.
- Use MONTH as a foundation for building dynamic reports, monthly summaries, and time-based analysis that automatically adapts to changing data.
Pro Tips
Use MONTH with YEAR to create a unique month identifier: =YEAR(A1)&"-"&TEXT(MONTH(A1),"00") produces "2024-03" for sorting and grouping.
Impact : Enables consistent month identification across complex datasets and improves data organization for analysis and reporting.
Combine MONTH with SUMPRODUCT for conditional monthly analysis: =SUMPRODUCT((MONTH($A$2:$A$100)=3)*($B$2:$B$100)) sums all March values without array formula complexity.
Impact : Simplifies complex conditional calculations and makes formulas more readable and maintainable across different Excel versions.
Create a dynamic month filter using MONTH with a helper column: =IF(MONTH(A1)=MONTH(TODAY()),"Current Month","") highlights transactions from the current month.
Impact : Enables quick visual identification of current period data and supports automated filtering for time-sensitive analysis.
Use MONTH in data validation to restrict date entries: Create a rule checking =MONTH(A1)<=12 to ensure valid month values in date calculations.
Impact : Prevents data entry errors and maintains data integrity by automatically validating month values before they're used in calculations.
Useful Combinations
Extract Month and Create Monthly Summary
=SUMIF($A$2:$A$100,MONTH($A$2:$A$100)=3,$B$2:$B$100)Combines MONTH with SUMIF to sum all values in column B where the corresponding date in column A falls in March (month 3). This creates dynamic monthly summaries that automatically adjust based on date values.
Calculate Age in Months
=DATEDIF(A1,TODAY(),"M")While DATEDIF is more direct, combining MONTH with YEAR provides an alternative: =(YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1). This calculates precise months between a birth date and today, useful for HR and demographic analysis.
Create Fiscal Month Reporting
=IF(MONTH(A1)>=10,MONTH(A1)-9,MONTH(A1)+3)Combines MONTH with IF to convert calendar months to fiscal months when the fiscal year starts in October. This transforms month 10 to fiscal month 1, enabling fiscal period analysis and reporting aligned with business cycles.
Common Errors
Cause: The serial_number parameter contains text that Excel cannot recognize as a valid date, such as "March 15" without a year or improperly formatted date strings.
Solution: Verify the date format matches your system settings or use the DATE function to create explicit dates. For example, use =MONTH(DATE(2024,3,15)) instead of =MONTH("March 15"). Ensure cells containing dates are formatted as Date type, not Text.
Cause: The function name is misspelled as =MONT(A1) or =MONTHS(A1), or the formula is entered in an Excel version that doesn't support the MONTH function (extremely rare in modern versions).
Solution: Double-check the spelling: MONTH (not MONT or MONTHS). Verify you're using a supported Excel version (2007 or later). Check for accidental spaces or special characters in the formula bar.
Cause: The cell reference in the serial_number parameter points to a deleted column or row, breaking the formula link.
Solution: Restore the deleted column or row if possible. If the data is permanently gone, update the formula to reference valid cells. Use Find & Replace to locate and fix broken references across multiple formulas.
Troubleshooting Checklist
- 1.Verify the cell contains a date value, not text. Format the cell as Date (right-click > Format Cells > Date) if needed.
- 2.Check that the serial_number parameter is not empty or referencing a blank cell. Use IFERROR to handle missing dates gracefully.
- 3.Ensure date values fall within Excel's valid date range (January 1, 1900 to December 31, 9999). Dates outside this range cause errors.
- 4.Confirm the formula syntax is exactly =MONTH(serial_number) with proper parentheses and no extra spaces or special characters.
- 5.Test with a known date like =MONTH(DATE(2024,3,15)) to verify the function works. If this fails, check Excel version compatibility.
- 6.Use the ISNUMBER function to validate that your date cell contains a number: =ISNUMBER(A1) should return TRUE for valid dates.
Edge Cases
Leap year dates like February 29, 2024
Behavior: MONTH correctly returns 2 for February 29. The function only extracts the month component and ignores whether the year is a leap year.
No special handling needed; MONTH treats leap year dates the same as any other date.
Dates from different centuries (e.g., 1900-01-01 vs 2000-01-01)
Behavior: MONTH returns 1 for both dates since both fall in January, regardless of the century or year difference.
Solution: If you need to distinguish between dates in different years, combine MONTH with YEAR: =YEAR(A1)&"-"&MONTH(A1).
MONTH ignores year information entirely, so year-spanning analysis requires additional functions.
End-of-month dates (31st of months with 30 days)
Behavior: MONTH correctly identifies the month even for dates like January 31 or March 31. Excel handles month-end dates properly.
Use EOMONTH function if you specifically need the last day of a month for calculations.
Limitations
- •MONTH only returns the month number (1-12) and cannot directly display month names. Use TEXT function or CHOOSE for month name display.
- •The function ignores time components, making it unsuitable for time-based analysis. For time extraction, use HOUR, MINUTE, or SECOND functions separately.
- •MONTH cannot handle invalid dates or text strings that aren't recognized as dates by Excel. Data validation and error handling with IFERROR are necessary for robust formulas.
- •The function doesn't account for different calendar systems or fiscal years. For fiscal period analysis, you must manually adjust the month number using IF statements or lookup tables.
Alternatives
Compatibility
✓ Excel
Since 2007
=MONTH(serial_number) - Identical syntax across all versions from Excel 2007 through Excel 365.✓Google Sheets
=MONTH(date) - Fully compatible with Google Sheets using identical syntax and behavior.Google Sheets handles date formatting slightly differently, so ensure dates are recognized as date values rather than text strings.
✓LibreOffice
=MONTH(date) - LibreOffice Calc supports MONTH with the same syntax and functionality as Excel.