Power Query Problems

How to Count Overlapping Project Dates by Store in Excel

Nimra Malik Sep 27, 2026 869 views

Question details

The user needs to calculate the number of overlapping project date ranges for individual stores, returning a value of zero if a store only contains a single project.

How to Count Overlapping Project Dates by Store in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing project timelines and performing data analysis to identify how many overlapping project periods occur within specific stores.
Observed behavior: Requires a structured output that accurately counts concurrent date overlaps per store, bypassing single-project stores with a zero.

Before you start

Ensure your dataset has properly defined start and end date columns, and verify that all date cells are formatted as Dates rather than Text to allow for accurate mathematical comparisons.

Solution 1Recommended

Count Overlapping Dates Using Power Query

Power Query is the most robust method for grouping records by store and evaluating project date ranges without writing complex, nested formulas.

This method is highly recommended for large datasets because it processes date comparisons in the background and keeps your workbook lightweight.

By grouping the data first, you can easily compare the start and end dates of multiple projects associated with the same store.

Load data into Power Query

Select any cell inside your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Group records by store

In the Power Query Editor, select your Store column, go to the Home tab, and click 'Group By'. Choose 'All Rows' as the aggregation method to keep your project dates nested.

Add a custom overlap calculation

Go to 'Add Column' and select 'Custom Column'. Write a custom M code snippet to compare the nested project start and end dates within each store to identify overlaps.

Filter single projects

Add a conditional column or step that counts the total projects per store. If the project count is 1, set the overlap output to 0.

Load results to worksheet

Click 'Close & Load' on the Home tab to output the newly calculated overlapping date counts into a new Excel worksheet.

Count Overlapping Dates Using Power Query

Automated Updating: Once your Power Query is set up, you can simply click 'Refresh' on the Data tab to update the overlap counts whenever new project dates are added to the source table.

Solution 2

Calculate Overlaps with Dynamic Array Formulas

For users operating in Excel 365, dynamic array formulas utilizing CHOOSECOLS and SEQUENCE can directly calculate overlaps in the worksheet.

Seamless Data Processing

Analyze Project Dates Effortlessly with WPS Spreadsheet

WPS Office provides a powerful, free platform for handling complex data analysis. With complete support for advanced dynamic array formulas and date functions, you can easily calculate overlapping projects and manage timelines without slowing down your computer.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open your .xlsx file containing the store and project timeline data.
2. Input the evaluation formula: Select the target cell and enter your conditional overlap formula utilizing IF and SUM functions.
3. Apply and analyze: Drag the fill handle to apply the formula across all rows, instantly displaying the accurate overlap count for each store.

Fully compatible with Microsoft Excel formulas, Power Query logic, and .xlsx file formats.Rich library of built-in date, time, and dynamic array functions for advanced calculations.Lightweight software architecture that runs smoothly even when processing massive datasets.Free to use with an intuitive, tabbed interface that makes data management simple.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my overlapping date formula return an error?

Ensure that your start and end date cells are formatted as Dates. If they are formatted as General or Text, the spreadsheet software cannot mathematically evaluate the greater-than or less-than conditions properly, resulting in #VALUE! errors.

Can I visually highlight overlapping dates instead of counting them?

Yes. You can use Conditional Formatting with a custom formula to highlight rows. Set a rule that triggers when a project's start date is less than the end date of another project within the same store grouping.

What if a store has only one project?

The recommended formula includes an IF statement (e.g., IF(COUNT(B3:I3)=2, 0)) which counts the date entries. If there is only one start and end date pair, the condition is met, bypassing the complex overlap check and outputting 0 directly.

Excel & Data Problems