Calculation Issues

Compare Dates by Month and Year Across Excel Sheets

Bushra ParveenBushra Parveen Nov 18, 2026 869 views

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.

Compare Dates by Month and Year Across Excel Sheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the matching 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","").

2
Add your calculation

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.

3
Adjust and fill down

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.

Use COUNTIFS to find a matching month in the Avg sheet
Date values matter: Formatting text to look like a date does not convert it into an Excel date. Convert text dates to actual dates before using the formula.
Compare dates in WPS Spreadsheet

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check that the Data and Avg sheets contain real date values.
  2. 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. 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. 4. Apply the formula: Drag the fill handle from C2 down the column to check the other dates, then save the workbook.
Compare dates across sheets without requiring the days to matchRun a custom calculation when a matching month is foundOpen and edit Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.