How Do You Use the Excel MONTH Formula to Analyze Data by Month?

Using the Excel MONTH formula is a fundamental skill for efficiently grouping, filtering, and analyzing date-based data without manual effort. This function automatically extracts the month number (1-12) from any valid date entry, enabling powerful data summarization and reporting capabilities. For professionals in recruitment, this is particularly useful for tracking hiring cycles, applicant volume by month, or budget reporting.

What is the Excel MONTH Function and How Does It Work?

The MONTH function is a date and time function in Excel designed to return the month component of a date as a number between 1 (January) and 12 (December). Its primary use case is to transform a full date into a simple month number, which can then be used for sorting, filtering, or as a criterion in other formulas. The basic syntax is =MONTH(serial_number), where the serial_number is the cell reference or date you want to extract from. For example, if cell A2 contains the date 09-Nov-1989, the formula =MONTH(A2) will return the number 11.

How Can You Extract the Month from Different Date Formats?

Excel stores dates as serial numbers, where each integer represents a unique day (e.g., January 1, 1900, is serial number 1). The MONTH function interprets these serial numbers. You can apply it to various date formats commonly found in spreadsheets:

• From a Cell Reference: This is the most common method. If your date is in cell D9, the formula is =MONTH(D9).
• From a Date Stored as Text: You can use the DATEVALUE function to convert a text string into a serial number first: =MONTH(DATEVALUE("09-Nov-1989")).
• Directly from a Date: You can nest the DATE function within MONTH: =MONTH(DATE(1989,11,9)).

Pro Tip: To apply the formula to an entire column, double-click the fill handle (the small square in the bottom-right corner of the selected cell) for automatic population.

When Should You Use the TEXT Function Instead of MONTH?

While the MONTH function returns a number, the TEXT function offers more flexibility for reporting by displaying the month as a name. This is ideal for creating more readable reports or presentations. The syntax uses format codes: "mmm" for an abbreviated name (e.g., "Nov") and "mmmm" for the full name (e.g., "November").

• =TEXT(D12, "mmm") yields "Nov".
• =TEXT(D12, "mmmm") yields "November".

Based on our assessment experience, using the TEXT function is preferable for final report outputs, while the MONTH function's numerical result is better for backend calculations and conditional logic.

How Can You Summarize Data by Month Using SUMIF?

A powerful application of the MONTH function is combining it with the SUMIF function to summarize values, such as recruitment spend or number of hires, by month. This process involves two steps:

1. First, create a helper column using the MONTH function to extract the month number for each date entry.
2. Second, use the SUMIF function to add values that match a specific month number.

For instance, if your month numbers are in column E (E15:E28) and the corresponding recruitment costs are in column D (D15:D28), the formula to sum all costs for March (month 3) would be: =SUMIF(E15:E28, 3, D15:D28)

This combination automates monthly aggregation, saving significant time compared to manual filtering and summing.

To effectively use Excel's MONTH function:

• Understand Date Serial Numbers to troubleshoot unexpected results.
• Use a Helper Column for month extraction before performing complex summaries.
• Combine with TEXT for Readability in client-facing or executive reports.
• Leverage SUMIF for Data Analysis to quickly calculate monthly totals for budgets or KPIs.