Chart & Visualization Issues

How to Automatically Update Excel Gantt Chart Progress by Date

John Wilson Sep 25, 2026 869 views

Question details

The user needs to automatically calculate and display task completion percentages in an Excel Gantt chart based on the current date, start date, and end date.

How to Automatically Update Excel Gantt Chart Progress by Date

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking project progress dynamically using a spreadsheet-based Gantt chart.
Observed behavior: The goal state is to have the task progress bar and chart colors update automatically as each day passes, visually reflecting the elapsed time without requiring manual data adjustments.

Before you start

Ensure your project schedule includes dedicated 'Start Date' and 'End Date' columns for each task, and verify that these cells are properly formatted as Date values rather than plain text.

Solution 1Recommended

Calculate Elapsed Progress with Formulas and Data Bars

Use a combination of the TODAY() function to calculate elapsed time and conditional formatting data bars to visually update the Gantt chart.

To ensure the progress bar updates automatically as time passes, you need a formula that compares today's date against the task's start and end dates. By nesting this calculation within MIN and MAX functions, you can restrict the output strictly between 0% and 100%, preventing errors for future or fully completed tasks.

Calculate the Task Progress Percentage

In the progress column for your first task (e.g., cell D2), enter the formula: =MAX(0, MIN(1, (TODAY()-B2)/(C2-B2))). Assume B2 is the Start Date and C2 is the End Date. Drag this formula down to apply it to all tasks.

Format Cells as Percentage

Select the cells containing your new formula. Right-click, choose 'Format Cells', select 'Percentage' from the Number tab, and click 'OK'.

Apply Conditional Formatting Data Bars

With the percentage cells still selected, go to the 'Home' tab on the ribbon. Click 'Conditional Formatting', hover over 'Data Bars', and select a solid or gradient fill color.

Customize the Data Bar Rules

To ensure the bars scale correctly from 0% to 100%, click 'Conditional Formatting' > 'Manage Rules'. Edit your Data Bar rule, set the 'Minimum' type to 'Number' with a value of 0, and the 'Maximum' type to 'Number' with a value of 1.

Calculate Elapsed Progress with Formulas and Data Bars

Automatic Daily Updates: Because this method uses the TODAY() function, your Gantt chart progress bars will automatically recalculate and visually step forward each day you open the workbook.

Solution 2

Create a Dynamic Stacked Bar Gantt Chart

Use a stacked bar chart with 'Completed' and 'Remaining' duration series to create a traditional chart object that updates automatically.

Easily Automate Gantt Charts in WPS Spreadsheet

WPS Spreadsheet provides powerful date functions and intuitive conditional formatting, allowing you to easily build and automate project management Gantt charts.

1. Open Your Project File: Launch WPS Spreadsheet and open your project schedule containing task start and end dates.
2. Apply Progress Formula: In your progress column, enter the formula =MAX(0, MIN(1, (TODAY()-StartDate)/(EndDate-StartDate))) to calculate the current completion rate.
3. Format as Percentage: Select the formula cells, right-click, choose 'Format Cells', and set the number format to 'Percentage'.
4. Add Conditional Formatting: Navigate to the 'Home' tab, click 'Conditional Formatting', select 'Data Bars', and choose a color to visually represent the progress.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced dynamic date formulas like TODAY() and NETWORKDAYS()Rich built-in conditional formatting and custom data bar toolsFree, lightweight, and user-friendly spreadsheet solution

Frequently Asked Questions

Why does my Gantt chart progress not update when I open the file the next day?

The TODAY() function updates when the workbook recalculates. If it hasn't updated, press the F9 key to manually force a recalculation. To fix this permanently, ensure automatic calculation is enabled by going to the Formulas tab, clicking 'Calculation Options', and selecting 'Automatic'.

How can I prevent the progress calculation from showing negative numbers for future tasks?

You can prevent negative numbers by wrapping your formula in the MAX function. Using =MAX(0, (TODAY()-StartDate)/(EndDate-StartDate)) ensures that if a task hasn't started yet (meaning the calculation would be negative), the formula will output exactly 0%.

Is there a way to calculate Gantt chart progress using only working days?

Yes. Instead of standard subtraction for total days, use the NETWORKDAYS() function to calculate working days between dates, which automatically excludes weekends. For example, replace (EndDate-StartDate) with NETWORKDAYS(StartDate, EndDate).

Excel & Data Problems