Populate calendar day cell with schedule information

[calvin771]

Book1
	A	B	C	D
1	Date	Time	Completed	Name
2	7/1/2022	9:45		John Doe
3	7/1/2022	9:45		Jane Doe
4	7/2/2022	11:00		Mr. Excel
Data

Using the above example data in Excel 365, I want to populate a calendar-like sheet similar to the below example:

Book1
	A	B	C	D	E	F	G
1	July 2022
2
3	26	27	28	29	30	1	2
4						9:45 John Doe 9:45 Jane Doe	11:00 Mr. Excel
5	3	4	5	6	7	8	9
6
July

I am attempting to use the TEXTJOIN function to accomplish this but am running into multiple issues. The first issue is that the times will not pull over formatted correctly (i.e. 9:45 is pulled over as 0.40625) and the second is that I need to return the values per row as the example above shows and not per column (e.g., incorrect formatted times followed by names). An example array formula is below:

=CONCAT(TEXTJOIN(REPT(" ",5),TRUE,IF(Data!$A$2:$A$4=July!F3,July!$B$2:$B$4,"")),TEXTJOIN(CHAR(10),TRUE,IF(Data!$A$2:A4=July!F3,Data!$D$2:$D$4,"")))

I'm not proficient enough with VBA if that's what is required so am looking for some assistance. Thanks in advance.

 

[Fluff]

How about

=TEXTJOIN(CHAR(10),,TEXT(FILTER(Data!$B$2:$B$4,Data!$A$2:$A$4=F3),"hh:mm")&REPT(" ",5)&FILTER(Data!$D$2:$D$4,Data!$A$2:$A$4=F3))

 

[calvin771]

How about

=TEXTJOIN(CHAR(10),,TEXT(FILTER(Data!$B$2:$B$4,Data!$A$2:$A$4=F3),"hh:mm")&REPT(" ",5)&FILTER(Data!$D$2:$D$4,Data!$A$2:$A$4=F3))

That worked great. Thanks a ton!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[calvin771]

As a followup, I got #CALC! entries in empty cells until adding the last argument to the FILTER function:

=TEXTJOIN(CHAR(10),,TEXT(FILTER(Data!$B$2:$B$4,Data!$A$2:$A$4=F3,""),"hh:mm")&REPT(" ",5)&FILTER(Data!$D$2:$D$4,Data!$A$2:$A$4=F3,""))

Again, thanks a ton!