Function Problems

How to Compare the Latest Six Months with the Previous Year in Excel

Phi Hung Vo Oct 10, 2026 869 views

Question details

The user needs an automatically updating formula to determine if a date falls within the last six months or the exact same six-month window from the previous year.

How to Compare the Latest Six Months with the Previous Year in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic date-based reports where data is compared over rolling periods without requiring manual date updates each month.
Observed behavior: The user needs a logical formula based on the current date that evaluates existing dates in a column and flags them accordingly (e.g., returning 'Yes' or 'No').

Before you start

Ensure your target column contains valid Excel serial dates rather than text-formatted dates. You can verify this by temporarily changing the cell format to 'General' to see if a numerical value appears.

Solution 1Recommended

Use a Dynamic Formula with EDATE and TODAY

Combine logical functions (IF, AND, OR) with date functions (EDATE, TODAY) to define dynamic rolling date ranges.

This method creates two distinct time windows based on the current date. The EDATE function dynamically subtracts months from today's date, establishing the boundaries for the most recent six months and the equivalent six months from a year ago.

Select the result cell

Click on an empty cell next to your date data, for example, cell C2 (assuming your date is in cell B2).

Enter the nested formula

Type the following formula: =IF(OR(AND(B2>=EDATE(TODAY(),-6),B2<=TODAY()),AND(B2>=EDATE(TODAY(),-18),B2<=EDATE(TODAY(),-12))),"Yes","No") and press Enter.

Apply the formula to the column

Select cell C2 again, hover over the small green square in the bottom-right corner, and double-click or drag it down to apply the logic to all the dates in your list.

Use a Dynamic Formula with EDATE and TODAY

Adjusting Period Boundaries: If your reporting periods use different inclusive dates, you can adjust the mathematical signs (such as changing >= to >) or modify the EDATE month values (e.g., -6, -18, -12) to fit your exact business logic.

Advanced Spreadsheet Tool

Effortlessly Manage Complex Date Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced logical and date functions, including EDATE and TODAY. It seamlessly executes nested formulas to help you track historical metrics and build dynamic automated reports.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates you want to compare.
2. Input the comparison formula: Select the target cell and paste the exact EDATE and TODAY formula provided above. WPS Spreadsheet handles the syntax natively.
3. Auto-fill the remaining rows: Use the AutoFill handle at the bottom right of the active cell to drag the formula down, instantly categorizing all your historical data.

100% compatible with Microsoft Excel formulas and date formattingProcesses large datasets and volatile functions (like TODAY) with high performanceFamiliar user interface ensuring a zero-learning-curve transitionCompletely free to use with cross-platform synchronization

microsoft office alternative - wps office

Frequently Asked Questions

Why is my EDATE formula returning a #VALUE! error?

A #VALUE! error typically indicates that the date in your reference cell (e.g., B2) is stored as text rather than a valid date. Select the problematic cells, go to the 'Home' tab, and format them as 'Short Date'.

Will the formula update automatically tomorrow?

Yes. Because the formula relies on the TODAY() function, Excel automatically recalculates the current date every time you open the workbook or force a calculation, maintaining a rolling six-month window.

How can I change the formula to compare a 3-month window instead?

To compare a 3-month period, update the month arguments in the EDATE function. Use -3 instead of -6 for the current window, and change the previous year's window arguments to -15 and -12.

Excel & Data Problems