serial_number - Required. The date from which you want to extract the month. Can be a date value, cell reference, or text date that Excel recognizes.
| A | B | |
|---|---|---|
| 1 | Date | Month Number |
| 2 | 2025-01-15 | |
| 3 | 2024-12-31 | |
| 4 | Formula: | =MONTH(A2) 1 |
Extract month from date in A1
=MONTH("2025-03-15") → 3
Get current month number
=MONTH(TODAY()) → 1 (if January)
Convert date to quarter
=MONTH("2025-05-10") → Q2
Flag current month records
=IF(MONTH(date)=1) → "Current"
Group transaction dates by month number for monthly sales analysis and revenue tracking. MONTH extracts month numbers from transaction timestamps, enabling pivot tables to aggregate sales by month, compare year-over-year monthly performance, identify seasonal sales patterns, and create month-based commission calculations for sales teams. This pattern is fundamental for retail analytics, e-commerce dashboards, and financial consolidation reports where monthly grouping is required.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Amount | Month |
| 2 | 2025-01-10 | 1200 | |
| 3 | 2025-02-15 | 1500 | |
| 4 | Formula: | =MONTH(A2) 1 |
Convert calendar dates to fiscal quarters using MONTH combined with mathematical operations. This function provides the month number which can be divided by 3 and rounded up to assign quarters (Q1-Q4). Essential for financial planning and analysis teams, accounting departments creating quarterly P&L statements, and business intelligence reports that align with fiscal year calendars. This pattern handles both calendar year and fiscal year quarter calculations efficiently.
| A | B | C | |
|---|---|---|---|
| 1 | Transaction | Date | Quarter |
| 2 | Order #1234 | 2025-01-20 | |
| 3 | Order #1235 | 2025-05-10 | |
| 4 | Formula: | ="Q"&ROUNDUP(MONTH(B2)/3,0) Q1 |
❌ The Problem:
✅ Solution:
=MONTH(DATE(2025,1,15))Use DATE function to create valid date from year, month, and day components, or reference cells containing actual date values. MONTH requires a proper date serial number, not text month names.
❌ The Problem:
✅ Solution:
=MONTH(A1&"-01")Append "-01" to create a complete date before extracting month, or ensure cells contain full date values. The formula works best with complete date serial numbers.
❌ The Problem:
✅ Solution:
=MONTH(DATE(2025,1,20))Use DATE function to be explicit about the date you want, or format cells as dates to avoid confusion. Excel MONTH function interprets numbers as date serial numbers, where 1 = January 1, 1900.
Master these MONTH Function variants:
From basics to advanced - AI generates perfect formulas instantly.