How to Check Whether Two Date Ranges Overlap with a WPS Spreadsheet Formula
Question details
The user needs a WPS Spreadsheet formula that checks whether an ownership period overlaps a reporting period and returns a blank for an overlap or a hyphen for no overlap.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing acquisition-to-sale dates against reporting period start and end dates in a worksheet.
- Observed behavior
- The goal is for the formula to return a blank when the date ranges overlap and a hyphen when they do not overlap.
Make sure the ownership and reporting period dates are entered as real date values, not text. Confirm that the reporting period start is in H5 and the end is in H6 before applying the formula.
Use the IF and AND Formula to Check Date Range Overlap
Use this formula when the reporting period start is in H5, the end is in H6, and the ownership dates are in C7 and D7.
The AND condition checks whether the ownership period intersects the reporting period. If both comparisons are true, the ranges overlap and the formula returns a blank.
Click the cell where you want the overlap indicator to appear, such as H7.
Type =IF(AND($C7<=H$6,$D7>=H$5),"","-") and press Enter.
Verify that the cell shows a blank for overlapping dates or a hyphen for no overlap.
Copy the Formula Across Reporting Period Columns
Use this after entering the formula to check the same ownership period against multiple reporting periods.
Check Overlapping Date Ranges in WPS Spreadsheet
Use WPS Spreadsheet’s familiar Excel-compatible formulas to compare acquisition and reporting periods and automatically return an overlap indicator. The same cell references, fill handle, and date comparisons work without changing your existing .xlsx files.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the worksheet that contains the acquisition and sale dates.
- 2. Select the result cell: Click the cell where the blank or hyphen should appear, next to the first ownership period.
- 3. Enter the overlap formula: Type =IF(AND($C7<=H$6,$D7>=H$5),"","-") and press Enter.
- 4. Fill the formula across reporting periods: Use the fill handle to copy the formula across the reporting period columns as needed.
- 5. Verify the indicators: Check that overlapping periods show a blank and non-overlapping periods show a hyphen.
Frequently Asked Questions
What does the formula =IF(AND($C7<=H$6,$D7>=H$5),"","-") do?
It returns a blank when the ownership period intersects the reporting period and a hyphen when there is no overlap. The AND function checks both required overlap conditions.
Does the formula count matching start or end dates as an overlap?
Yes. The <= and >= operators include the boundary dates, so ranges that meet exactly at one date are treated as overlapping.
Why are some references written with dollar signs?
$C7 and $D7 lock the ownership date columns while allowing row changes. H$5 and H$6 lock the reporting period rows while allowing column changes when you copy the formula.
Can I copy this formula across multiple reporting period columns?
Yes. The mixed references make the formula adjust to each reporting period column while continuing to read the correct ownership dates.
What should I check if the formula returns unexpected results?
Confirm all date cells are real dates, not text, and verify that the reporting period start and end cells match the H5 and H6 references in the formula.
