Compare Dates by Month and Year Across Excel Sheets
Question details
Check whether a date on the Data sheet matches a month and year in a list on the Avg sheet, and perform a calculation when a match exists.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has dates on a Data sheet and a list of month-and-year values on an Avg sheet.
- Observed behavior
- The desired result is to identify a matching month and year regardless of the day, then run a calculation for that row.
Make sure Data!B2 and the values in Avg!B2:B100 are real Excel dates, not text. A month-and-year display such as Jan 2025 should still be stored as a complete date.
Use COUNTIFS to find a matching month in the Avg sheet
Use this method when each date on Data needs to be checked against a list of dates on Avg.
The formula counts dates from the first day of the month up to, but not including, the first day of the next month. This matches both the month and year, regardless of the day or time.
On the Data sheet, select C2 and enter =IF(COUNTIFS(Avg!$B$2:$B$100,">="&EOMONTH(B2,-1)+1,Avg!$B$2:$B$100,"<"&EOMONTH(B2,0)+1)>0,"Match","").
In the formula bar, replace "Match" with the calculation to run, such as D2*2. Leave the final "" in place to return a blank when there is no match.
Change both Avg!$B$2:$B$100 references if your list uses a different range. Then drag the fill handle from C2 down the Data sheet to check the remaining rows.

Compare dates in corresponding rows with EOMONTH
Use this shorter formula when each Data row should be compared only with the same row on Avg.
Match Dates by Month and Year in WPS Spreadsheet
Use COUNTIFS and EOMONTH in WPS Spreadsheet to check a date against a list on another sheet and run a calculation when the month and year match.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check that the Data and Avg sheets contain real date values.
- 2. Enter the formula: On the Data sheet, select C2 and enter =IF(COUNTIFS(Avg!$B$2:$B$100,">="&EOMONTH(B2,-1)+1,Avg!$B$2:$B$100,"<"&EOMONTH(B2,0)+1)>0,"Match","").
- 3. Set the calculation: In the formula bar, replace "Match" with the calculation you want to perform, such as D2*2, and adjust the Avg range if needed.
- 4. Apply the formula: Drag the fill handle from C2 down the column to check the other dates, then save the workbook.

Frequently Asked Questions
Does the formula match the year as well as the month?
Yes. The COUNTIFS formula checks the date range for the month containing Data!B2, so January 2024 does not match January 2025.
Can the dates on Avg have different days of the month?
Yes. Any real date within the same month and year matches, including dates with a time value.
What if the formula does not find a date that looks like a match?
Check Data!B2 and the relevant Avg cells for text values. Convert text to real dates, then confirm that both Avg range references include the matching row.
How do I calculate a value instead of displaying Match?
In the IF formula, replace "Match" with your calculation, such as D2*2. The final "" keeps the result blank when no matching month is found.
What if the Avg list has more than 99 dates?
Expand both Avg!$B$2:$B$100 references to cover the full list, such as Avg!$B$2:$B$500, before filling the formula down.




