Dynamic Array solution for a Gantt Chart

[dejhantulip]

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

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021	11/01/2021	12/01/2021	13/01/2021
2		01/01/2021	04/01/2021	1	1	1	1	0	0	0	0	0	0	0	0	0
3		06/01/2021	09/01/2021	0	0	0	0	0	1	1	1	1	0	0	0	0
4
Main

Cell Formulas
Range	Formula
D2:P3	D2	=(B2<=$D$1:$P$1)*(C2>=$D$1:$P$1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:P3	Expression	=D2=1	text	NO

 

[mamady]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			01-Apr-21	02-Apr-21	03-Apr-21	04-Apr-21	05-Apr-21	06-Apr-21	07-Apr-21	08-Apr-21	09-Apr-21	10-Apr-21	11-Apr-21	12-Apr-21	13-Apr-21	14-Apr-21	15-Apr-21	16-Apr-21
2	03-Apr-21	07-Apr-21	0	0	1	1	1	1	1	0	0	0	0	0	0	0	0	0
3	06-Apr-21	13-Apr-21	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0
4	03-Apr-21	09-Apr-21	0	0	1	1	1	1	1	1	1	0	0	0	0	0	0	0
5	13-Apr-21	16-Apr-21	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1
Sheet1 (2)

Cell Formulas
Range	Formula
C2:R5	C2	=(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
Cell	Condition		Cell Format	Stop If True
C2:R5	Cell Value	=0	text	NO
C2:R5	Cell Value	=1	text	NO

Regards

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021	06/01/2021	07/01/2021	08/01/2021	09/01/2021	10/01/2021	11/01/2021	12/01/2021	13/01/2021
2		01/01/2021	04/01/2021	1	1	1	1	0	0	0	0	0	0	0	0	0
3		06/01/2021	09/01/2021	0	0	0	0	0	1	1	1	1	0	0	0	0
4		04/01/2021	07/01/2021	0	0	0	1	1	1	1	0	0	0	0	0	0
5		08/01/2021	15/01/2021	0	0	0	0	0	0	0	1	1	1	1	1	1
6		03/01/2021		0	0	0	0	0	0	0	0	0	0	0	0	0
7		05/01/2021	10/01/2021	0	0	0	0	1	1	1	1	1	1	0	0	0
8				0	0	0	0	0	0	0	0	0	0	0	0	0
9				0	0	0	0	0	0	0	0	0	0	0	0	0
10				0	0	0	0	0	0	0	0	0	0	0	0	0
Main

Cell Formulas
Range	Formula
D2:P10	D2	=(B2:B10<=$D$1:$P$1)*(C2:C10>=$D$1:$P$1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:P10	Expression	=D2=1	text	NO

 

[dejhantulip]

Amazing!! Thank you very much!!! <3

 

[Fluff]

Glad we could help & thanks for the feedback.