Date Planner from Start and End Dates

[wcself81]

Folks,
I can't help but feel there is an elegant solution to this one but I'm on the verge of creating a set of horrendous formulae that I'd love to avoid if possible......

As an output, I'm trying to produce a holiday planner view in a calendar style format of names down the left, dates across the top with a "1" in the cells that indicate a holiday is in for that day.

The Input is a list of holiday requests with a name with a start and end date only.

So for example; I want to be able to show "1" under the 10th, 11th, 12th, 13th, 14th and 15th of Jan for Joe, because Joe has input the 10th to the 15th for a holiday (As shown on the left).

I've tried a few combinations of countifs but can't get them to work.

What formula can i put in the yellow cells on the right hand side that would read the holiday requests on the left hand side correctly?

 

[hagia_sofia]

Hello, please test the following (in I5):

=DROP(REDUCE("",H5:H11,LAMBDA(x,y,VSTACK(x,
LET(
a,B6:B7,
b,C6:C7,
c,D6:D7-b+1,
d,SEQUENCE(,XLOOKUP(y,a,c),XLOOKUP(y,a,b)),
IF(ISNUMBER(XLOOKUP(I4:AG4,d,d)),1,""))))),1)

 

[Cubist]

Few more options:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2
3
4						Pull-down formula		1/1/25	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25	1/9/25	1/10/25	1/11/25	1/12/25	1/13/25	1/14/25	1/15/25	1/16/25	1/17/25	1/18/25	1/19/25	1/20/25	1/21/25	1/22/25	1/23/25	1/24/25	1/25/25
5	Name	Start	End				John	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Joe	1/10/25	1/15/25				Jim	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	John	1/1/25	1/4/25				Joe	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0
8							David	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9							Jack	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10							Brian	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11							Sean	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12
13
14								1/1/25	1/2/25	1/3/25	1/4/25	1/5/25	1/6/25	1/7/25	1/8/25	1/9/25	1/10/25	1/11/25	1/12/25	1/13/25	1/14/25	1/15/25	1/16/25	1/17/25	1/18/25	1/19/25	1/20/25	1/21/25	1/22/25	1/23/25	1/24/25	1/25/25
15						Single array->	John	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16							Jim	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17							Joe	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0
18							David	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19							Jack	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20							Brian	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21							Sean	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet5

Cell Formulas
Range	Formula
I5:AG11	I5	=LET(x,XLOOKUP(H5,$B$6:$B$7,$C$6:$D$7),d,I$4:AG$4,IFNA((d>=INDEX(x,1))*(d<=INDEX(x,2)),0))
I15:AG21	I15	=DROP(REDUCE("",H15:H21,LAMBDA(a,b,VSTACK(a,LET(x,XLOOKUP(b,$B$6:$B$7,$C$6:$D$7),d,$I$14:$AG$14,IFNA((d>=INDEX(x,1))*(d<=INDEX(x,2)),0))))),1)
Dynamic array formulas.

 

[kvsrinivasamurthy]

Try. In I5

=LET(a,A6:C7,b,H5:H10,c,I4:AG4,MAKEARRAY(ROWS(b),COLUMNS(c),LAMBDA(ro,cl,LET(ra,MATCH(INDEX(b,ro),INDEX(a,,1),0),z,IF((INDEX(a,ra,2))<=INDEX(c,cl)*(INDEX(a,ra,3)>=INDEX(c,cl)),1,""),IFERROR(z,"")))))