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?

 

[wcself81]

Okay, Part 2. Apologies for the pile of posts. You can tell I've had a go at this for a Sunday afternoon....

I've managed to get the previous solutions all working, so my thanks again for those that have given me a way ahead. They work really well for the extended dataset I've tried them with.

However, the complication that's arose is where an individual has two separate holiday requests in for the time period being assessed.

See attached image. I'm going to work round the issue by reducing the time period being looked at to 7 days. It will resolve the vast majority of the issues it is causing me. However, if someone has a Monday requested and a Friday, the solution will miss the Friday....... I'll just live with it.

But if anyone has any further blinding flashes of inspiration, please let me know!

 

[Cubist]

Try:

Book2
	B	C	D	E			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								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	1	1	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	John	1/17/25	1/18/25				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
Sheet3

Cell Formulas
Range	Formula
I5:AG11	I5	=LET(f,FILTER($C$6:$D$8,$B$6:$B$8=$H5),BYCOL(I$4:AG$4,LAMBDA(bc,IFERROR(--OR(MAP(INDEX(f,,1),INDEX(f,,2),LAMBDA(a,b,MEDIAN(a,b,bc)=bc))),0))))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:AG11	Cell Value	=1	text	NO