Holiday Calendar

[krisso]

I am trying to create a holiday calendar for my office that when the holiday is booked in the table (list) it highlights the days taken in the below calanders. I was pretty sure I could do this by index match until that didnt work that is, so now I am stuck. Has anyone got an idea of how I can achieve this.

Thanks

Date How many Days Whos Holiday Book Until
19-Aug 5 Chris 23-Aug
19-Mar 1 Grant 19-Mar
28-Mar 0.5 Grant 28-Mar
04-Apr 1 Chris 04-Apr
26-Apr 0.5 Grant 26-Apr
22-Jul 5 Grant 26-Jul
29-Jul 1 Chris 29-Jul
07-May 0.5 Grant 07-May
30-May 0.5 Grant 30-May
10-Jun 0.5 Grant 10-Jun
14-Jun 0.5 Grant 14-Jun
04-Jul 1 Chris 04-Jul

April
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Chris
Grant

May
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Chris
Grant

June
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Chris
Grant

July
Name 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Chris
Grant

 

[AlanY]

Thanks Mumps and AlanY

https://docs.google.com/spreadsheets/d/1iBilVOblGWctuEwBTGrnKXvZrnxGZuiFUD3ayRSf-rE/edit?usp=sharing

Hopefully this works

Basically there are 2 tabs, one where the data is entered and the second where I want to populate the calendar against the relevant staff member

Thanks

i've adopted the formula in March on post#7 to your file

https://drive.google.com/file/d/1k_ezOuGdIqNn0gR7ps3NKv26NmVyT_Q2/view?usp=sharing

when you copy the formula to new month you need to adjust the row reference of that month as I done for June (from March)

 

[krisso]

i've adopted the formula in March on post#7 to your file

https://drive.google.com/file/d/1k_ezOuGdIqNn0gR7ps3NKv26NmVyT_Q2/view?usp=sharing

when you copy the formula to new month you need to adjust the row reference of that month as I done for June (from March)

Hi Alan,

Sorry I can't see the formula, have I missed something?

Thanks
Chris

 

[AlanY]

Hi Alan,

Sorry I can't see the formula, have I missed something?

Thanks
Chris

silly me, sent the wrong link

https://drive.google.com/file/d/1gdsKpDo3bP0UtYBd0nOUA8o_0gRzTtn8/view?usp=sharing

 

[Fluff]

@AlanY
Can you please post yur solution to the board, so that everyone can see it without having to download files, as per Rule#4.
Thanks

 

[AlanY]

@AlanY
Can you please post yur solution to the board, so that everyone can see it without having to download files, as per Rule#4.
Thanks

yes, sir.

Book1
	A	B	C	D	E
1
2	Holidays Remaining
3	Date	How many Days	Whos Holiday	Book Until	Return to Work
4	19-Aug	5	Chris	23-Aug	27/8/2019
5	19-Mar	1	Grant	19-Mar	20/3/2019
6	28-Mar	0.5	Grant	28-Mar	28/3/2019
7	04-Apr	1	Chris	04-Apr	5/4/2019
8	26-Apr	0.5	Grant	26-Apr	26/4/2019
9	22-Jul	5	Grant	26-Jul	29/7/2019
10	29-Jul	1	Chris	29-Jul	30/7/2019
11	07-May	0.5	Grant	07-May	7/5/2019
12	30-May	0.5	Grant	30-May	30/5/2019
13	10-Jun	0.5	Grant	10-Jun	10/6/2019
14	14-Jun	0.5	Grant	14-Jun	14/6/2019
15	04-Jul	1	Chris	04-Jul	5/7/2019
Holiday 2019-2020

Book1
	A	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
1	March
2	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	Chris
4	Grant																			X
5
6	April
7	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
8	Chris
9	Grant
10
11	May
12	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
13	Chris
14	Grant
15
16	June
17	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
18	Chris
19	Grant														X
Calendar 2019-2020

Cell Formulas
Range	Formula
B3	=IF(SUMPRODUCT(--($A3=Table_2[Return to Work]),--(B$2>=Table_2[Whos Holiday]),--(B$2<=Table_2[Date]))>0,"X","")
B18	=IF(SUMPRODUCT(--($A18=Table_2[Return to Work]),--(B$17>=Table_2[Whos Holiday]),--(B$17<=Table_2[Date]))>0,"X","")

 

[Fluff]

Many thanks.

If I use that formula in B3 & drag it across, it changes the table columns to
=IF(SUMPRODUCT(--($A3=Table_2[Date]),--(C$2>=Table_2[Book Until]),--(C$2<=Table_2[How many Days]))>0,"X","")
then
=IF(SUMPRODUCT(--($A3=Table_2[How many Days]),--(D$2>=Table_2[Return to Work]),--(D$2<=Table_2[Whos Holiday]))>0,"X","")

How do you stop that?

 

[AlanY]

Many thanks.

If I use that formula in B3 & drag it across, it changes the table columns to
=IF(SUMPRODUCT(--($A3=Table_2[Date]),--(C$2>=Table_2[Book Until]),--(C$2<=Table_2[How many Days]))>0,"X","")
then
=IF(SUMPRODUCT(--($A3=Table_2[How many Days]),--(D$2>=Table_2[Return to Work]),--(D$2<=Table_2[Whos Holiday]))>0,"X","")

How do you stop that?

yes, that wasn't right. thanks.
not quite sure how to absolute reference a column in table, but this should work

Book1
	A	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
1	March
2	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
3	Chris
4	Grant																			X
5
6	April
7	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
8	Chris
9	Grant
10
11	May
12	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
13	Chris
14	Grant
15
16	June
17	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
18	Chris
19	Grant										X				X
20
21	July
22	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
23	Chris
24	Grant
25
26	August
27	Name	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
28	Chris
29	Grant
30
Calendar 2019-2020

Cell Formulas
Range	Formula
B3	=IF(SUMPRODUCT(--($A3='Holiday 2019-2020'!$C$1:$C$15),--(B$2>='Holiday 2019-2020'!$A$1:$A$15),--(B$2<='Holiday 2019-2020'!$D$1:$D$15))>0,"X","")
B18	=IF(SUMPRODUCT(--($A18='Holiday 2019-2020'!$C$1:$C$15),--(B$17>='Holiday 2019-2020'!$A$1:$A$15),--(B$17<='Holiday 2019-2020'!$D$1:$D$15))>0,"X","")