How to Use MONTH in Excel

Key Insights

The MONTH function extracts the month number (1-12) from any valid Excel date, making it essential for financial reporting, data segmentation, and time-based analysis
Combining MONTH with functions like IF, SUMIF, and TEXT transforms raw dates into actionable business intelligence, from quarterly reports to month-over-month comparisons
Excel stores dates as serial numbers starting from January 1, 1900, which is why MONTH can extract the month component from what appears to be formatted text

Introduction to the MONTH Function

The MONTH function is one of Excel’s fundamental date manipulation tools, designed to extract the month component from any date value and return it as a number between 1 and 12. While this might sound simple, it’s a workhorse function that powers everything from financial quarter calculations to sales trend analysis.

I use MONTH constantly when building dashboards, creating pivot table groupings, and filtering datasets by time periods. It’s particularly valuable when you have transaction data spanning multiple months and need to aggregate, compare, or filter based on monthly periods. Financial analysts rely on it for monthly close reports, sales teams use it to track performance by month, and operations teams apply it to identify seasonal patterns.

The real power of MONTH isn’t in using it alone—it’s in combining it with other Excel functions to build sophisticated date-based logic and calculations.

Basic MONTH Syntax and Simple Examples

The MONTH function uses straightforward syntax:

=MONTH(serial_number)

The serial_number argument is any valid Excel date. Understanding how Excel handles dates internally is crucial here: Excel stores dates as sequential serial numbers, where January 1, 1900 is serial number 1, January 2, 1900 is 2, and so on. When you type “3/15/2024” into a cell, Excel converts it to the serial number 45369 behind the scenes, then displays it as a formatted date.

Here’s a basic example extracting the month from a hardcoded date:

=MONTH("2024-03-15")

This returns 3 because March is the third month. However, hardcoding dates is rarely practical. In real-world scenarios, you’ll reference cells containing dates:

=MONTH(A2)

If cell A2 contains the date 2024-03-15, this formula returns 3. If A2 contains 2024-12-25, it returns 12.

You can also use MONTH with the result of other date functions:

=MONTH(DATE(2024, 3, 15))

This also returns 3. The DATE function creates a valid date serial number, which MONTH then processes.

Combining MONTH with Other Date Functions

The MONTH function becomes significantly more powerful when combined with Excel’s other date functions. This is where you move from simple extraction to dynamic date calculations.

To get the current month number, combine MONTH with TODAY:

=MONTH(TODAY())

This formula always returns the current month number. If today is any day in March, it returns 3. Tomorrow, if it’s still March, it still returns 3. On April 1st, it automatically returns 4.

You can build complex date calculations by combining MONTH with DATE and YEAR. For example, to calculate the first day of the next month from any given date:

=DATE(YEAR(A2), MONTH(A2)+1, 1)

If A2 contains March 15, 2024, this formula:

Extracts the year (2024) using YEAR(A2)
Extracts the month (3) using MONTH(A2) and adds 1 to get 4
Uses DATE to construct April 1, 2024

This technique is invaluable for calculating payment due dates, subscription renewals, or any scenario requiring month-boundary calculations.

To find the last day of the current month, use this pattern:

=DATE(YEAR(A2), MONTH(A2)+1, 1)-1

This calculates the first day of next month, then subtracts one day to get the last day of the current month.

Practical Applications: Filtering and Grouping by Month

MONTH truly shines in conditional logic and data analysis scenarios. One common use case is categorizing dates into quarters:

=IF(MONTH(A2)<=3, "Q1", IF(MONTH(A2)<=6, "Q2", IF(MONTH(A2)<=9, "Q3", "Q4")))

This nested IF statement checks the month number and assigns the appropriate quarter. For a date in February (month 2), it returns “Q1”. For August (month 8), it returns “Q3”.

A cleaner approach uses a lookup table, but the direct IF method is transparent and easy to audit.

For financial analysis, you often need to sum values for specific months. Here’s where things get tricky: SUMIF doesn’t work directly with MONTH because SUMIF evaluates the actual cell values, not formula results. Instead, create a helper column with the month number:

In column D:

=MONTH(A2)

Then use SUMIF:

=SUMIF(D:D, 3, C:C)

This sums all values in column C where the corresponding month (in column D) equals 3.

For a more dynamic solution without helper columns, use SUMPRODUCT:

=SUMPRODUCT((MONTH($A$2:$A$100)=3)*($C$2:$C$100))

This multiplies two arrays: one checking if the month equals 3 (returns TRUE/FALSE, treated as 1/0), and one with the values to sum. Only rows where the month is March contribute to the total.

To count how many transactions occurred in a specific month:

=SUMPRODUCT((MONTH($A$2:$A$100)=3)*1)

For month-over-month comparisons, combine MONTH with conditional formatting or formulas:

=IF(MONTH(A2)=MONTH(TODAY()), "Current Month", "Prior Period")

Converting Month Numbers to Names

Numbers are great for calculations, but reports need readable month names. The TEXT function converts dates to formatted text strings:

=TEXT(A2, "MMMM")

If A2 contains any date in March, this returns “March”. The format code “MMMM” (four M’s) produces the full month name.

For abbreviated month names:

=TEXT(A2, "MMM")

This returns “Mar” for March, “Jan” for January, etc.

You can also combine the month name with the year:

=TEXT(A2, "MMMM YYYY")

This returns “March 2024” for a date in March 2024.

Important: TEXT returns a text string, not a date. You can’t use it in further date calculations. Use TEXT only for final display purposes.

If you only have a month number (not a full date), you need to construct a date first:

=TEXT(DATE(2024, A2, 1), "MMMM")

If A2 contains 3, this creates the date March 1, 2024, then extracts “March”.

Common Errors and Troubleshooting

The most frequent error with MONTH is #VALUE!, which occurs when Excel can’t interpret the input as a valid date. This happens when:

The cell contains text that looks like a date but isn’t stored as a date serial number
The cell contains an invalid date format
The cell is completely empty (though Excel sometimes returns #NUM! for this)

To handle text dates, use DATEVALUE to convert them:

=MONTH(DATEVALUE(A2))

If A2 contains the text “March 15, 2024” (stored as text, not a date), DATEVALUE converts it to a proper date serial number.

For robust error handling, wrap MONTH in IFERROR:

=IFERROR(MONTH(A2), "")

This returns a blank cell if MONTH encounters an error, preventing error values from cascading through your spreadsheet.

To handle blank cells specifically:

=IF(A2="", "", MONTH(A2))

This checks if A2 is empty before attempting to extract the month.

Another common issue occurs with dates formatted as text. If you import data from external systems, dates often arrive as text strings. You’ll recognize this because the dates align left in cells (text alignment) instead of right (number alignment). Convert these using DATEVALUE or Excel’s “Text to Columns” feature with date formatting.

Regional date format differences cause problems too. A date like “3/4/2024” means March 4 in the US but April 3 in Europe. When using hardcoded dates in formulas, use the unambiguous ISO format:

=MONTH("2024-03-04")

This always interprets as March 4, 2024, regardless of regional settings.

Putting It All Together

The MONTH function is deceptively simple but incredibly versatile. Master these patterns and you’ll handle most date-based analysis tasks:

Extract months for grouping and categorization
Combine with conditional functions for date-based logic
Use with aggregation functions through helper columns or SUMPRODUCT
Convert to readable names with TEXT for reporting
Handle errors gracefully with IFERROR and validation checks

The key is understanding that MONTH is rarely used in isolation. It’s a building block that combines with other functions to solve real business problems. Whether you’re building a sales dashboard, analyzing seasonal trends, or automating monthly reports, MONTH gives you the foundation to work with temporal data effectively.

Start simple, test your formulas with known dates, and build complexity incrementally. Once you’re comfortable with basic MONTH operations, the advanced combinations become intuitive.