Creating a calendar from a data set.

[Erald]

Hello,

Would I be able to create a calendar from a data set as shown in the attachment? I would like to be able to change the date on the data set and the name associated with it to appear in the calendar. (Note that I have put the names on the calendar manually, based on the data set information). Thank you in advance for your help!

Regards,
Erald

 

[JamesCanale]

I reorganized the way the 'days' are kept - it makes it easier if you keep it like data in a neat table.

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J
1				June 2024
2				Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3	Name	Day								01
4	DHHS P	6/17/2024
5	DHHS P	6/24/2024
6	DHHS P	6/25/2024
7	TITLE IV	6/10/2024		02	03	04	05	06	07	08
8	TITLE IV	6/17/2024
9	TITLE IV	6/24/2024
10	NSF	6/12/2024
11	NSF	6/24/2024		09	10	11	12	13	14	15
12	NSF	6/25/2024			TITLE IV	Education	NSF
13	Education	6/11/2024
14	Education	6/18/2024
15	Education	6/25/2024		16	17	18	19	20	21	22
16					DHHS P	Education
17					TITLE IV
18
19				23	24	25	26	27	28	29
20					DHHS P	DHHS P
21					TITLE IV	NSF
22					NSF	Education
23				30
24
25
26
Sheet19

Cell Formulas
Range	Formula
D3	D3	=IF(WEEKDAY($D$1)=1,$D$1,"")
E3	E3	=IF(WEEKDAY($D$1)=2,$D$1,IF(D3<>"",D3+1,""))
F3	F3	=IF(WEEKDAY($D$1)=3,$D$1,IF(E3<>"",E3+1,""))
G3	G3	=IF(WEEKDAY($D$1)=4,$D$1,IF(F3<>"",F3+1,""))
H3	H3	=IF(WEEKDAY($D$1)=5,$D$1,IF(G3<>"",G3+1,""))
I3	I3	=IF(WEEKDAY($D$1)=6,$D$1,IF(H3<>"",H3+1,""))
J3	J3	=IF(WEEKDAY($D$1)=7,$D$1,IF(I3<>"",I3+1,""))
D4:J4,D24:J24,E20:F22,D20,G20:J20,E16:E17,D16,F16:J16,D12:J12,D8:J8	D4	=IFERROR(IF(D3="","",FILTER(Table8[Name],Table8[Day]=D3)),"")
D7,D15,D11	D7	=J3+1
E7:J7,E15:J15,E11:J11	E7	=D7+1
D19	D19	=IF(MONTH(J15+1)=MONTH($D$1),J15+1,"")
E19:J19,E23:J23	E19	=IFERROR(IF(MONTH(D19+1)=MONTH($D$1),D19+1,""),"")
D23	D23	=IFERROR(IF(MONTH(J19+1)=MONTH($D$1),J19+1,""),"")

 

[Erald]

What if I have two tables as shown in the picture and I need to pull the data from both of them into the calendar, how would the formula look like? I don't seem to get the correct one.

 

[JamesCanale]

vstack should be your friend here.

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1						June 2024
2						Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3	Name	Day	Name2	Day3								01
4	DHHS P	6/17/2024	A	6/18/2024
5	DHHS P	6/24/2024	A	6/25/2024
6	DHHS P	6/25/2024	A	6/26/2024
7	TITLE IV	6/10/2024	B	6/11/2024		02	03	04	05	06	07	08
8	TITLE IV	6/17/2024	B	6/18/2024
9	TITLE IV	6/24/2024	B	6/25/2024
10	NSF	6/12/2024	B	6/13/2024
11	NSF	6/24/2024	C	6/25/2024		09	10	11	12	13	14	15
12	NSF	6/25/2024	C	6/26/2024			TITLE IV	Education	NSF	B
13	Education	6/11/2024						B
14	Education	6/18/2024
15	Education	6/25/2024				16	17	18	19	20	21	22
16							DHHS P	Education
17							TITLE IV	A
18								B
19						23	24	25	26	27	28	29
20							DHHS P	DHHS P	A
21							TITLE IV	NSF	C
22							NSF	Education
23								A
24								B
25								C
26
27
28						30
29
30
31
Sheet19

Cell Formulas
Range	Formula
F3	F3	=IF(WEEKDAY($F$1)=1,$F$1,"")
G3	G3	=IF(WEEKDAY($F$1)=2,$F$1,IF(F3<>"",F3+1,""))
H3	H3	=IF(WEEKDAY($F$1)=3,$F$1,IF(G3<>"",G3+1,""))
I3	I3	=IF(WEEKDAY($F$1)=4,$F$1,IF(H3<>"",H3+1,""))
J3	J3	=IF(WEEKDAY($F$1)=5,$F$1,IF(I3<>"",I3+1,""))
K3	K3	=IF(WEEKDAY($F$1)=6,$F$1,IF(J3<>"",J3+1,""))
L3	L3	=IF(WEEKDAY($F$1)=7,$F$1,IF(K3<>"",K3+1,""))
F4:L4,F29:L29,I20:I21,H20:H25,G20:G22,F20,J20:L20,H16:H18,G16:G17,F16,I16:L16,H12:H13,F12:G12,I12:L12,F8:L8	F4	=IFERROR(IF(F3="","",FILTER(VSTACK(Table8[Name],Table8[Name2]),VSTACK(Table8[Day],Table8[Day3])=F3)),"")
F7,F15,F11	F7	=L3+1
G7:L7,G15:L15,G11:L11	G7	=F7+1
F19	F19	=IF(MONTH(L15+1)=MONTH($F$1),L15+1,"")
G19:L19,G28:L28	G19	=IFERROR(IF(MONTH(F19+1)=MONTH($F$1),F19+1,""),"")
F28	F28	=IFERROR(IF(MONTH(L19+1)=MONTH($F$1),L19+1,""),"")