fill in mulitple excel cells based on multiple cell values similar to a gantt chart

Question

I would like to create a Gantt chart like with excel based on task start and duration cell values. I have an excel as below:

Item	Start	Duration(months)	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
1	Q1	6
2	Q2	3
3	Q2	1

• Start column indicates the quarter-calender the task starts.
• Duration indicates the number of months the task can take to complete.

Based on these two value I would like to have a formula in columns Jan-Aug to fill with x appropriately

Desired output:

Item	Start	Duration(months)	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
1	Q1	6	x	x	x	x	x	x
2	Q2	3				x	x	x
3	Q2	1				x

I dabbled with XLOOKUP and OFFSET with no luck. Tried creating defined names as in Microsoft template with no luck

Answer 1

You can try the following formula in cell D2:

=LET(months, MONTH(D1:O1), dur, C2:C4, Qs, B2:B4, 
  QsUx, SORT(UNiQUE(Qs)), start, XLOOKUP(Qs, QsUx, SEQUENCE(ROWS(QsUx),,1,3)),
  end, start + dur - 1,
  GEN_X, LAMBDA(idx, MAP(start, end, LAMBDA(ss, ee, 
    LET(month, INDEX(months,,idx), IF(MEDIAN(ss, ee, month) = month, "x",""))))),
  DROP(REDUCE("", months, LAMBDA(acc, m, HSTACK(acc, GEN_X(m)))),,1)
)

and here is the output:

Note: The formula generates the entire grid, there is no need to expand the formula.

Explanation

It uses the following two ideas:

• To populate the grid using the MEDIAN function. Check the answer provided by @JosWoolley: Sum unique day count within a date ranges.
• DROP/REDUCE/HSTACK pattern to iterate over all the month's columns to generate the x's for a given column. Check the answer provided by @DavidLeal to the question: how to transform a table in Excel from vertical to horizontal but with different lengths.

The rest is just to prepare/transform the input data in the format we want. The LET function is used for easy reading and composition.

The row with the months: D1:O1 was generated in date format as follows:

=EDATE(DATE(2022,1,1), SEQUENCE(1,12,0))

Now the months variable will have the corresponding months via the MONTH function, it returns the following sequence of values: 1,2,..,12.

QsUx has the unique names of quarters sorted. Then we can calculate the start date as follow:

XLOOKUP(Qs, QsUx, SEQUENCE(ROWS(QsUx),,1,3))

SEQUENCE generates the following sequence: 1,4,7,..etc. (as many values as rows have QsUx). Representing the starting month of each quarter.

Note: This is just one approach. The start can be obtained, by extracting the number of the Quarter and building a sequence: 1, prev. value + 3, for example. Alternatively, since we have only four quarters just a simple constant array with all the Qs and their corresponding start dates. The approach used in the main formula is more generic because the Gantt can be expanded to more than one year and it still works.

Having the start date, then we can calculate the corresponding end date as follows: start + dur - 1.

The user LAMBDA function GEN_X generates the x's values for a given column of months indicated by the idx as input argument (representing the corresponding month).

Finally, we use the DROP/REDUCE/HSTACK pattern to append each column to complete the grid.

Note: The solution assumes no excel version constraints as per the tag listed in the question, so all existing excel functions are available. If you have some limitations for example you don't have the DROP function, let me know to try to find some alternative.