How to Check if Month and Year Match Between Dates in Excel

The Necessity of Date Matching in Data Analysis

Analyzing time-series data often requires comparing dates based on specific granularities, such as checking if two events occurred within the same month and year, regardless of the day or time. This is a common requirement in financial reporting, project management, and quality control, where linking specific actions to a reporting period is essential. Fortunately, Excel provides powerful functions that allow users to perform these intricate comparisons efficiently. By combining simple extraction functions with fundamental concatenation techniques, we can build a robust formula to determine if the month and year components of two distinct dates are identical.

The method presented here relies on isolating the numerical values for the month and year from each date, combining them into a unique identifier, and then comparing these identifiers. This technique ensures that the comparison is exact and ignores irrelevant data points like the day, hours, or minutes, providing a clean and definitive Boolean logic result. Understanding this core mechanism is foundational for advanced date manipulation within spreadsheets.

To check definitively if the month and year components match between two dates in Excel, we utilize the following powerful and concise formula, which relies on the combination of the MONTH function and the YEAR function:

The Core Formula for Month and Year Comparison

The most straightforward approach to verifying the month and year match between two dates—for instance, the date stored in cell A2 and the date in cell B2—involves joining the two extracted numerical components and testing for equality.

=MONTH(A2)&YEAR(A2)=MONTH(B2)&YEAR(B2)

This specific formula performs two key actions: first, it extracts and concatenates the month and year from cell A2; second, it repeats this process for cell B2. Finally, it compares the two resultant strings. If the concatenated strings are identical (e.g., “12023” equals “12023”), the formula returns TRUE. Conversely, if there is any mismatch in the month or the year, it returns FALSE, providing an immediate boolean result regarding the equality of the date periods.

This formula is highly effective because it treats the combined month and year as a single, unique identifier. By converting the numerical month and year into a text string using the ampersand operator (&), we ensure that the comparison is based purely on the period definition, effectively ignoring the day component of the original date entry. This foundational understanding allows for the practical application of this comparison technique across large datasets, enhancing data integrity checks.

Practical Implementation: Generating Boolean Results (TRUE/FALSE)

To demonstrate the utility of this formula, consider a scenario where we have two distinct columns of dates that require cross-referencing to see if they fall within the same calendar month and year. This setup is common when comparing transaction dates against reconciliation dates, or planned dates against actual completion dates.

Suppose we are working with the following data table in Excel, organized into a “Date List 1” and “Date List 2”:

Our goal is to populate column C with a Boolean logic result (TRUE or FALSE) indicating whether the dates in corresponding rows of columns A and B share the identical month and year combination. We initiate this process by entering the comparison formula into cell C2, which targets the first pair of dates:

=MONTH(A2)&YEAR(A2)=MONTH(B2)&YEAR(B2)

Once the formula is correctly entered in C2, the subsequent step is to efficiently apply this logic across the entire dataset. This is achieved by using the autofill handle—clicking and dragging the formula down to each remaining cell in column C. This action automatically adjusts the cell references (A2 to A3, B2 to B3, and so on), ensuring that every date pair is evaluated individually, yielding a complete column of results.

The result clearly illustrates which pairs of dates align monthly and yearly, providing immediate insight into the temporal relationship between the two lists.

Visualizing the Basic Match/No Match Scenario

Following the application of the formula and dragging it down column C, the spreadsheet should visually confirm the expected outputs based on the comparison performed for each row. The results demonstrate the power of combining the MONTH function and the YEAR function.

As shown in the completed example, column C now exclusively returns either TRUE or FALSE. A TRUE result signifies that the date in column A and the corresponding date in column B share the exact same month and year combination. Conversely, FALSE indicates a discrepancy in either the month, the year, or both. For instance, if A2 is December 2023 and B2 is January 2024, the result would be FALSE because while the month components (12 and 1) and the year components (2023 and 2024) are extracted correctly, the concatenated identifier strings differ, failing the final equality test.

While TRUE and FALSE outputs are computationally precise and ideal for further logical functions (like filtering or conditional formatting), they may not be the most user-friendly format for reports intended for a general audience. This leads us to an important enhancement: customizing the output using the IF function.

Enhancing Output Readability: Using the IF Function

For scenarios where the audience requires more descriptive feedback than standard Boolean logic, the entire date-matching comparison formula can be nested within Excel’s powerful IF function. The IF function allows us to specify custom text strings to be returned based on the outcome of the logical test. This makes reports instantly more accessible and easier to interpret without needing prior knowledge of spreadsheet formulas.

To achieve a textual output like “Match” or “Do Not Match,” we structure the IF formula as follows. The initial date comparison formula serves as the logical test (the first argument of the IF function). If the comparison evaluates to TRUE, the function returns the specified text for the “value if true”; if it evaluates to FALSE, it returns the “value if false.”

=IF(MONTH(A2)&YEAR(A2)=MONTH(B2)&YEAR(B2), "Match", "Do Not Match")

This modified structure significantly improves the clarity of the result column. Instead of interpreting TRUE/FALSE, users instantly see a relevant status message. The following visual evidence showcases the implementation and resulting improved output when using the nested IF condition for the date comparison:

As clearly demonstrated, column C now returns either Match or Do Not Match, providing a human-readable summary of whether the month and year components align between the corresponding cells in columns A and B. This method is particularly recommended for dashboard reporting and data validation tasks where quick assessment is paramount.

A Deep Dive into the Concatenation Technique

Understanding the mechanism behind the core formula is essential for troubleshooting and adapting it to different scenarios. Recall the central formula:

=MONTH(A2)&YEAR(A2)=MONTH(B2)&YEAR(B2)

The efficacy of this formula hinges on two distinct Excel functions: the MONTH function, which extracts the month number (1 to 12), and the YEAR function, which extracts the four-digit year. These two numerical results are then joined using the ampersand symbol (&), which is the standard operator for concatenation in Excel, effectively creating a single text string.

To illustrate, if cell A2 contains the date 12/15/2023:

1. The MONTH(A2) function returns 12.
2. The YEAR(A2) function returns 2023.
3. The concatenation MONTH(A2)&YEAR(A2) joins these to produce the unique identifier “122023”.

Similarly, if cell B2 contains the date 12/01/2023, the operation for MONTH(B2)&YEAR(B2) also results in the text string “122023”. The final comparison, “122023” = “122023”, yields TRUE. This confirms that even though the day components (15 and 01) were different, the month and year match, satisfying the original requirement.

Crucially, the use of concatenation avoids the mathematical inaccuracies that could arise if one were to try adding or multiplying the month and year, which would not produce a unique, reliable identifier for comparison. This logic is consistently applied down the entire column, guaranteeing reliable verification for every row in the dataset.

Alternative Methods for Date Comparison

While the concatenation method is robust and easy to read, Excel offers several alternative approaches for performing partial date comparisons. These methods often involve nesting the date extraction functions within a logical AND function, or using the specialized EOMONTH function.

Using the AND Function

Instead of joining the values, you can explicitly check both components independently using the AND function. This method is often preferred by those who find concatenation less intuitive. The formula looks like this:

=AND(MONTH(A2)=MONTH(B2), YEAR(A2)=YEAR(B2))

This formula requires two separate logical tests to return TRUE: the months must match, AND the years must match. If either condition is FALSE, the entire AND function returns FALSE. This approach maintains high readability and directly expresses the required dual condition.

Using the EOMONTH Function

A third highly powerful method leverages the EOMONTH (End of Month) function. The EOMONTH function takes a date and returns the serial number of the last day of the month, zero months away (i.e., the current month’s end). By comparing the EOMONTH values for two dates, we inherently check if they fall within the same month and year because the end-of-month date will be identical only if the period is identical.

=EOMONTH(A2, 0)=EOMONTH(B2, 0)

This formula is arguably the cleanest and most concise option for comparing month and year simultaneously. It is particularly valued by advanced Excel users for its efficiency and elegance, although it does require familiarity with Excel’s date serial number system.

Common Pitfalls and Best Practices

When implementing date comparison formulas, users must be aware of certain common pitfalls to ensure data accuracy and formula integrity. The primary challenge involves ensuring that both inputs (A2 and B2) are recognized by Excel as valid date serial numbers. If one or both cells contain dates stored as text strings—a frequent occurrence when importing data—the MONTH function and YEAR function may return errors or unexpected results.

To mitigate this, always perform a preliminary check on the data formatting. A quick way to verify if a cell holds a true date is to temporarily change its formatting to “General.” If a large number (the date serial number) appears, the cell contains a valid date. If the cell retains the date appearance (e.g., “1/15/2023”), it is likely stored as text and requires conversion using functions like DATEVALUE or the Text to Columns feature.

Furthermore, when using the concatenation method, always confirm that your year extraction yields a four-digit year. While the YEAR function typically returns four digits, some legacy systems or locale settings might default to two digits, which could lead to an unreliable comparison (e.g., comparing 12023 to 1223). However, the modern YEAR function generally guarantees the full four-digit output, making the presented formula reliable for current spreadsheet environments. Adhering to these best practices ensures that your date comparisons are not only correct but also scalable and maintainable.