Function Problems

How to Create an Excel Gantt-Style Table for a 15-Day Quality-Control Period

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to create an automated Gantt-style Excel table that tracks planned and received objects through a 15-working-day quality control cycle, automatically updating totals as items enter and exit the period.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Managing and visualizing a rolling 15-day quality control process for incoming objects.
Observed behavior: The user requires a dynamic layout that accurately calculates 15 working days, adds new items to the active tracking count, and removes them from the total once the period expires.

Before you start

Ensure your source data has accurate columns for 'Planned Date' and 'Received Date', as valid date formats are essential for conditional formatting and the WORKDAY formulas to function correctly.

Solution 1Recommended

Build a Dynamic 15-Day Gantt Table Using Formulas and Conditional Formatting

Use Excel's WORKDAY function to calculate the 15-day expiration date and apply Conditional Formatting to visually plot the Gantt timeline.

By utilizing the WORKDAY function, you can automatically skip weekends when calculating the 15-day quality control period. Pairing this with Conditional Formatting allows the spreadsheet to automatically color the cells corresponding to the active timeframe, acting as a visual Gantt chart.

Set up the tracking headers

Create columns for Item ID, Planned Date, Received Date, and Expiration Date. To the right of these columns, create a timeline by entering sequential dates as column headers.

Calculate the 15-day expiration date

In the Expiration Date column, use the formula =WORKDAY(C2, 15) (assuming C2 is your Received Date). This calculates the end of the 15-working-day period while skipping weekends.

Apply Conditional Formatting for the Gantt timeline

Highlight the empty grid under your timeline dates. Go to Home > Conditional Formatting > New Rule, and select 'Use a formula to determine which cells to format'.

Enter the formatting formula

Input =AND(E$1>=$C2, E$1<=$D2) (assuming E1 is your first timeline date, C2 is the Received Date, and D2 is the Expiration Date). Click Format, choose a fill color, and click OK.

Automate the active items count

To calculate how many objects are currently in the 15-day period, use the formula =COUNTIFS(C:C,"<="&TODAY(),D:D,">="&TODAY()). This counts objects received on or before today that have not yet expired.

Automated Expiration: Because the conditional formatting relies on the dynamic expiration date and current dates, objects will automatically drop off the visual Gantt chart and total counts when their 15-day period concludes.

Smart Project Tracking

Create Professional Gantt Charts Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting, advanced date formulas like WORKDAY, and built-in project tracking templates to effortlessly manage your 15-day quality control timelines.

1. Open a new tracker: Launch WPS Spreadsheet and create a new blank workbook, or search for 'Gantt' in the Templates section to start with a pre-built layout.
2. Input date functions: Use the built-in WORKDAY function to calculate your 15-day quality control limit without manually counting days.
3. Visualize with formatting: Navigate to the Home tab and select Conditional Formatting to highlight the 15-day tracking period across your spreadsheet automatically.

Fully compatible with Microsoft Excel (.xlsx) formats and date formulas.Extensive conditional formatting rules for accurate visual timeline tracking.Built-in Gantt chart templates available for free in the WPS Template library.Lightweight, fast, and free to use for seamless project management.

microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude specific holidays from the 15-day quality control calculation?

You can use the WORKDAY function with a custom holiday range. Set up a separate list of holiday dates, and use the formula =WORKDAY(start_date, 15, holiday_range). This will calculate 15 working days while skipping both weekends and your specified holidays.

Why is my conditional formatting Gantt chart highlighting the wrong dates?

This usually happens due to incorrect cell locking (absolute vs. relative references). Ensure your formula locks the column for the start/end dates (e.g., $C2) and locks the row for the timeline header dates (e.g., E$1).

Can I automatically count how many items are currently in quality control?

Yes, you can use the COUNTIFS function. A formula like =COUNTIFS(Received_Date_Range, "<="&TODAY(), Expiration_Date_Range, ">="&TODAY()) will count only the items currently within their active 15-day period.

Excel & Data Problems