How to Include Original and Extension Dates in an Excel Gantt Chart
Question details
The user needs to combine original project dates and extended project dates into a single, continuous timeline bar in an Excel Gantt chart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating or updating a project management Gantt chart where some tasks have been extended, and the timeline needs to accurately reflect the entire duration from the earliest original start date to the latest extended end date.
- Observed behavior
- The Gantt chart currently displays either the original timeline dates or the extension dates, failing to show the complete project duration as a unified bar.
Ensure your Excel worksheet contains clearly separated and properly formatted date columns for the Original Start Date, Original End Date, Extension Start Date, and Extension End Date.
Update Conditional Formatting Using MIN and MAX Formulas
Use a conditional formatting formula that calculates the earliest start date and the latest end date to create a continuous Gantt chart bar covering the entire project duration.
By combining the MIN and MAX functions within your logical AND statement, Excel can evaluate multiple date columns simultaneously. This guarantees the Gantt bar stretches from the absolute earliest start to the absolute latest finish.
Open your Excel Gantt chart and highlight all the cells in the timeline grid where the colored bars should appear.
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select Manage Rules. Select your existing Gantt chart rule and click Edit Rule, or click New Rule and choose 'Use a formula to determine which cells to format'.
In the formula box, enter the formula that uses MIN and MAX. For example: =AND(MIN($Q15,$S15)<=AC$14,MAX($R15,$T15)>=AC$14). Replace Q and S with your start date columns, R and T with your end date columns, and AC$14 with your timeline date header cell.
Click the Format button, go to the Fill tab, and choose the color for your extended Gantt bar. Click OK to apply the rule to your timeline.
Combine Dates Using a Nested IF Worksheet Formula
If your Gantt chart relies on in-cell formulas instead of conditional formatting to generate the timeline, adapt the cell formulas to use MAX/MIN functions.
Create Comprehensive Gantt Charts in WPS Spreadsheet
Easily combine original and extended project dates using WPS Spreadsheet. With full support for advanced conditional formatting and Excel-compatible formulas, tracking complete project timelines is straightforward and efficient.
- 1. Open your file: Launch WPS Spreadsheet and open your project tracking document.
- 2. Access Conditional Formatting: Select your timeline range, go to the Home tab, and click Conditional Formatting.
- 3. Apply the rule: Select 'New Rule', choose 'Use a formula...', input your MIN/MAX date formula, and set your desired fill color.
Frequently Asked Questions
Why isn't my conditional formatting formula updating the Gantt chart?
This usually happens due to incorrect absolute or relative cell references. Ensure that your column references for the start and end dates are locked with a dollar sign (e.g., $Q15), while the row references for your timeline dates are locked (e.g., AC$14).
Can I display original and extension dates in different colors on the Gantt chart?
Yes. Instead of combining them into one MIN/MAX formula, create two separate conditional formatting rules. Use one rule for the original dates (e.g., =AND($Q15<=AC$14, $R15>=AC$14)) with one color, and a second rule for the extension dates with another color.
What happens to the formula if a task doesn't have an extension date?
The MIN and MAX formula will still work if the extension date cells are blank, provided your spreadsheet treats the blank cells appropriately. To prevent unexpected zero-value errors, you can use an IF statement to check for blanks, such as =IF(ISBLANK($S15), $Q15, MIN($Q15, $S15)).
