WPS Spreadsheet: Fill Gantt Chart Cells Based on Dates and Work Duration
Question details
The user needs a formula that marks Gantt chart calendar cells with an X when each column date falls within the task's planned work period.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calendar dates are arranged across columns N through T, while the task duration and ending date are stored in columns G and H. The user wants to fill the task row automatically.
- Observed behavior
- The Gantt chart cells need to display X for dates inside the calculated work interval and remain blank for dates outside it.
Confirm that the calendar headers in N7:T7 are real spreadsheet dates rather than text, and verify that G contains the work duration while H contains the task end date. Check the first task row and adjust the row references if your sheet uses different columns.
Use a cell-by-cell formula and fill it across the Gantt row
Use this approach when each calendar cell should evaluate its own date and the formula needs to be copied across the row.
This formula compares the date in each calendar column with the task's calculated start boundary and end date. It returns X for dates in the work period and an empty string for all other dates.
In WPS Spreadsheet, select cell N11, or the first calendar cell in the task row.
Type =IF(AND(N$7>$H11-$G11,N$7<=$H11),"X","") in the formula bar or directly in cell N11, then press Enter.
Select N11 and drag the fill handle to the right through T11, or use Home > Fill > Right after selecting N11:T11.
Confirm that X appears only under calendar dates within the task period. If the calendar headers are on another row, replace N$7 with the correct header reference.
Use one dynamic array formula for the entire Gantt range
Use this option when your WPS Spreadsheet version supports dynamic arrays and you want one formula to populate multiple calendar cells.
Create Date-Based Gantt Markers in WPS Spreadsheet
WPS Spreadsheet can calculate each task's work interval and automatically place X markers beneath matching calendar dates. Enter the formula in the first task cell, fill it across the timeline, and keep the workbook compatible with Excel files.
- 1. Open the project schedule: Open the workbook in WPS Spreadsheet and locate the task row, duration or start data, end date, and calendar header dates.
- 2. Select the first calendar cell: Click the first blank Gantt chart cell, such as N11, where the first X should be calculated.
- 3. Enter the formula: Type =IF(AND(N$7>$H11-$G11,N$7<=$H11),"X","") and press Enter.
- 4. Fill across the timeline: Drag the fill handle from N11 through the final calendar column, such as T11, to apply the formula to the row.

Frequently Asked Questions
Which cells should contain the calendar dates for this Gantt formula?
The example assumes the calendar dates are in N7:T7. If your dates are in another row, replace N$7 with the corresponding first date cell and fill the formula across the calendar range.
Why does the formula use $H11-$G11?
The formula treats H11 as the task end date and G11 as the work duration, calculating the beginning boundary by subtracting the duration from the end date. If your sheet stores an explicit start date instead, compare the header date directly with that start-date cell and H11.
How do I apply the formula to additional task rows?
After filling the formula across the first row, select the completed range and drag the fill handle down, or copy and paste it into the following task rows. The row reference will update automatically.
What should I do if the formula displays numbers instead of dates?
Select the calendar header cells, open Home > Number Format, and choose Date. Also verify that the entries are recognized as dates rather than text copied from another source.
Can I use a dynamic array formula instead of copying across each cell?
Yes. In a dynamic-array-enabled version of WPS Spreadsheet, enter =IF((N$7:T$7>$H11-$G11)*(N$7:T$7<=$H11),"X","") in N11 and leave the cells through T11 empty for the results to spill.




