Chart & Visualization Issues

Keep Gantt Chart Entries Aligned with Dates in Excel

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Keep task or vacation information associated with the correct dates when the dates displayed in an Excel Gantt chart change.

Keep Gantt Chart Entries Aligned with Dates in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user changes the week dates displayed across a Gantt chart and wants schedule entries to follow their dates.
Observed behavior
Text or numbers typed directly into the chart grid stay in their original cells when the date headers change.
Before you start

Check which cells contain the timeline dates and the task start and end dates. Move any manually typed grid entries into source columns before replacing those grid cells with formulas.

Solution 1Recommended

Use source dates and formulas to populate the Gantt grid

Use this approach when task labels or vacation entries need to follow changing date headers.

Conditional formatting changes cell appearance; it does not move values typed into the cells. Store each entry and its dates in source columns, then let formulas and formatting display it in the grid.

1
Set up the source data

Enter each task or vacation label in column B, its start date in column E, and its end date in column F. Enter timeline dates in row 5, starting at G5.

2
Display labels by date

In G6, enter =IF(AND(G$5>=$E6,G$5<=$F6),$B6,"") and fill the formula across and down the Gantt grid. Each label will appear under headers that fall within its start and end dates.

3
Color the matching cells

Select the grid starting at G6, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(G$5>=$E6,G$5<=$F6), choose a fill color, and apply the rule.

4
Test the timeline

Change a date in row 5 and check that the labels and colored cells update. Adjust the cell references if your source columns or timeline start in different locations.

Use source dates and formulas to populate the Gantt grid
Weekly headers: The example checks each header date. If each header is the first day of a seven-day week and you want to show any overlap with that week, use =AND(G$5<=$F6,G$5+6>=$E6) as the condition in both the label formula and the formatting rule.
Work with Gantt charts in WPS

Keep Gantt Chart Dates and Labels in Sync in WPS Spreadsheets

Use source date columns, formulas, and conditional formatting in WPS Spreadsheets to display task labels against the correct timeline dates.

  1. 1. Open the schedule: Open WPS Spreadsheets and use Open to select your Excel Gantt chart workbook.
  2. 2. Organize the source columns: Enter task labels in column B, start dates in column E, end dates in column F, and timeline dates in row 5 starting at G5.
  3. 3. Add the label formula: In G6, enter =IF(AND(G$5>=$E6,G$5<=$F6),$B6,"") and fill it across and down the chart grid.
  4. 4. Format matching dates: Select the grid starting at G6. Use Home > Conditional Formatting to create a formula-based rule with =AND(G$5>=$E6,G$5<=$F6), and choose a fill color.
  5. 5. Check and save: Change a timeline date to verify the display, then choose File > Save As to save the workbook in XLSX format.
Display Gantt labels based on task start and end datesOpen and edit Microsoft Excel XLS and XLSX filesUse formulas and conditional formatting in a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why don't my typed Gantt entries move when I change the dates?

The entries are stored in fixed worksheet cells, while changing row 5 only changes the date headers. Put the entries in source columns and use formulas to show them under matching dates.

Can conditional formatting move text to another cell?

No. Home > Conditional Formatting controls appearance, such as fill color. Use a formula in the grid to display a label from a source cell.

How do I show a task that overlaps part of a week?

If G5 is the first day of a seven-day week, use =AND(G$5<=$F6,G$5+6>=$E6) as the condition in the grid formula and conditional formatting rule. It checks whether the task overlaps any day of that week.

Why did a task disappear after I changed the timeline?

The task may fall outside the dates currently shown in row 5. Check its start and end dates in columns E and F, then extend or change the displayed timeline.

Can I type directly into cells that contain Gantt formulas?

Typing into a formula cell replaces its formula. Edit the task label and dates in the source columns instead, or use a calendar template if direct entry is more important.