# Dynamic Array solution for a Gantt Chart

#### dejhantulip

##### New Member
Hey everyone!

I wanted to share a little situation I am dealing with.

So I have been using dynamic arrays for some time now and I gotta say they are indeed amazing!

Right now I am trying to make my Gantt Chart more dynamic, and I am stuck with the following problem.
So I need to generate an array of 1's and 0's (ones and zeroes) that check whether a calendar date is between the Start ("Inicio" sorry for the spanish in the sheet), and End ("Final").
So if the date on row 19 is indeed between the Inicio and Final, then output a 1, if not a 0.
You can see the formula I am using, however it is not dynamically adjusting. I have tried to use the M7 reference as M7# and it doesn't work either.

Any other ideas on how to solve this?
Maybe using a matrix function like MMULT?
Or something else?
Any ideas?

Any and all help will be deeply appreciate it

Have a great one!

ORLANDO

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFGHIJKLMNOP
101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/2021
201/01/202104/01/20211111000000000
306/01/202109/01/20210000011110000
4
Main
Cell Formulas
RangeFormula
D2:P3D2=(B2<=\$D\$1:\$P\$1)*(C2>=\$D\$1:\$P\$1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:P3Expression=D2=1textNO

##### Board Regular
Further to @Fluff suggestion, please check the below, if you a require dynamic formula that accommodates both columns and rows (you just enter it once without dragging):

Gantt Chart.xlsx
ABCDEFGHIJKLMNOPQR
101-Apr-2102-Apr-2103-Apr-2104-Apr-2105-Apr-2106-Apr-2107-Apr-2108-Apr-2109-Apr-2110-Apr-2111-Apr-2112-Apr-2113-Apr-2114-Apr-2115-Apr-2116-Apr-21
203-Apr-2107-Apr-210011111000000000
306-Apr-2113-Apr-210000011111111000
403-Apr-2109-Apr-210011111110000000
513-Apr-2116-Apr-210000000000001111
Sheet1 (2)
Cell Formulas
RangeFormula
C2:R5C2=(FILTER(\$A:\$A,ISNUMBER(\$A:\$A))<=FILTER(\$1:\$1,ISNUMBER(\$1:\$1)))*(FILTER(\$1:\$1,ISNUMBER(\$1:\$1))<=FILTER(\$B:\$B,ISNUMBER(\$B:\$B)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:R5Cell Value=0textNO
C2:R5Cell Value=1textNO

Regards

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMNOP
101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/2021
201/01/202104/01/20211111000000000
306/01/202109/01/20210000011110000
404/01/202107/01/20210001111000000
508/01/202115/01/20210000000111111
603/01/20210000000000000
705/01/202110/01/20210000111111000
80000000000000
90000000000000
100000000000000
Main
Cell Formulas
RangeFormula
D2:P10D2=(B2:B10<=\$D\$1:\$P\$1)*(C2:C10>=\$D\$1:\$P\$1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:P10Expression=D2=1textNO

#### dejhantulip

##### New Member
Amazing!! Thank you very much!!! <3

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
5
Views
457
Replies
2
Views
53
Replies
1
Views
242
Replies
5
Views
80
Replies
1
Views
162

1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back