Chart & Visualization Issues

How to Count a Project Phase for Every Month Until the Next Status Change

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to count and chart project phases over a timeline (monthly, quarterly, or yearly) by ensuring each status remains active in the calculation from its initial change date until the subsequent status change.

Product: Spreadsheet Software
Device & OS: not provided
Scenario: Tracking and visually charting project status phases across consecutive months, requiring the spreadsheet to fill the gaps between status change dates.
Observed behavior: Project statuses only display on their actual change date rather than bridging the gap across multiple months until a new status is recorded, making it difficult to generate a continuous timeline chart.

Before you start

Ensure all dates in your source data are formatted as valid numerical dates rather than text, and verify that all status changes are listed in chronological order.

Solution 1Recommended

Summarize Project Phases Using a Date Table and COUNTIFS

Create a continuous reporting timeline and use the COUNTIFS function to count how many projects were in a specific phase during each month.

To properly calculate active phases over time, you must calculate the end date of each phase. Once you have a definitive start and end date for every project phase, you can use a separate date table to evaluate if a given month falls within that timeframe.

Sort Data by Project and Date

Select your entire dataset. Go to the Data tab, click 'Sort', and set up two sorting levels: first by Project Name (A to Z), and second by the Change Date (Oldest to Newest).

Calculate the Phase End Date

Create a new column named 'End Date'. Use an IF formula to check if the next row belongs to the same project. If it does, subtract 1 day from the next row's start date; if it doesn't, use the TODAY() function or your project's final deadline.

Create a Reporting Date Table

On a new worksheet, create a list of consecutive months (e.g., Jan-2023, Feb-2023, Mar-2023) in the top row or first column to serve as your reporting timeline.

Apply the COUNTIFS Formula

In your date table, use the COUNTIFS function to count the phases. Set the criteria to count rows where the project phase matches, the phase Start Date is less than or equal to the reporting month's end, and the phase End Date is greater than or equal to the reporting month's start.

Chronological Order is Critical: Always double-check that your project data is sorted chronologically. If a project's status changes are out of order, the calculated end dates will be incorrect and skew your month-by-month chart.

Solution 2

Use a PivotTable to Group Phase Intervals

Leverage PivotTables to group your structured timeline data and create dynamic visualizations without writing complex array formulas.

Master Spreadsheets with WPS Office

Easily Track and Visualize Project Phases with WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools, advanced COUNTIFS functions, and dynamic PivotTables to help you track project phases over time efficiently. Best of all, it offers a familiar interface and seamless compatibility with Excel files.

1. Open Data in WPS Spreadsheet: Launch WPS Office and open your project dataset.
2. Sort Chronologically: Use the Data tab to apply a multi-level sort by Project and Date.
3. Calculate and Summarize: Add an End Date column, then use COUNTIFS or insert a PivotTable to summarize phase durations.
4. Insert Chart: Navigate to the Insert tab and select a chart type to visually report the active phases month-by-month.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatting.Advanced PivotTable features for quick timeline grouping and summarization.Built-in date calculation functions and intuitive multi-level sorting.Rich, professional visualization tools to chart project phases beautifully.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning zero for active phases?

This usually happens if your dates are stored as text instead of numbers. Select your date column, navigate to the Data tab, and use 'Text to Columns' to convert them to recognizable date values.

How do I ensure chronological order for my project data?

Highlight your dataset, go to the Data tab, and click 'Sort'. Add two sorting levels: first Sort by Project (A to Z), then click 'Add Level' and Sort by Date (Oldest to Newest).

Can I show overlapping project phases on the same timeline chart?

Yes. By using a PivotChart combined with properly calculated start and end dates, you can plot multiple overlapping series. Converting your data into a Gantt chart format is highly recommended for overlapping project timelines.

Excel & Data Problems