Seeking assistance to populate a list from a table

[auto.pilot]

I searched and can't find anything quite like this. I have a table with times the range of D3:L3 and dates starting in B4 and below. I'd like users of this workbook to add an 'x' in time slots that are available and open. Thereafter, I'd like some code or formulas that would populate the list shown in column N. The colors are simply for posting to this forum and will not be used.

I have written a formula that populates a single cell, for example from the x in cell D4, I wrote a formula that produces the desired date and time format in cell N4.

I am struggling to populate a list like what is shown in column N. I guess I'd prefer a code-based approach.

Thanks in advance for any and all assistance.

Jim

 

[auto.pilot]

I would prefer text if possible so that I can add the bullet point and a couple of spaces in the front. Thereafter, I will copy the list and paste it into an email.

Thanks

 

[Fluff]

How about

AvailableDates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		●	Available
3		Date	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM		EST
4	Thursday	06/07/2023	x			x			x				● Thursday 7/6 @ 9:00 AM EST
5	Friday	07/07/2023		x			x						● Thursday 7/6 @ 12:00 PM EST
6	Saturday	08/07/2023											● Thursday 7/6 @ 3:00 PM EST
7	Sunday	09/07/2023											● Friday 7/7 @ 10:00 AM EST
8	Monday	10/07/2023			x					x			● Friday 7/7 @ 1:00 PM EST
9	Tuesday	11/07/2023	x					x					● Monday 7/10 @ 11:00 AM EST
10	Wednesday	12/07/2023		x									● Monday 7/10 @ 4:00 PM EST
11	Thursday	13/07/2023											● Tuesday 7/11 @ 9:00 AM EST
12	Friday	14/07/2023											● Tuesday 7/11 @ 2:00 PM EST
13	Saturday	15/07/2023											● Wednesday 7/12 @ 10:00 AM EST
14	Sunday	16/07/2023
15	Monday	17/07/2023
16	Tuesday	18/07/2023
17	Wednesday	19/07/2023
18	Thursday	20/07/2023
19	Friday	21/07/2023
20	Saturday	22/07/2023
21	Sunday	23/07/2023
22	Monday	24/07/2023
23	Tuesday	25/07/2023
24	Wednesday	26/07/2023
25	Thursday	27/07/2023
26	Friday	28/07/2023
27	Saturday	29/07/2023
28	Sunday	30/07/2023
29	Monday	31/07/2023
Sheet1

Cell Formulas
Range	Formula
A4:A29	A4	=VLOOKUP(WEEKDAY(B4),$P$5:$Q$11,2,FALSE)
B4	B4	=TODAY()
B5:B29	B5	=B4+1
M4:M13	M4	=LET(d,TOCOL(IF(C4:K160="x",B2&" "&A4:A160&" "&TEXT(B4:B160,"m/d")&" @ ",1/0),2)&TOCOL(IF(C4:K160="x",C3:K3&" "&M3,1/0),2),FILTER(d,NOT(ISNA(d))))
Dynamic array formulas.

 

[auto.pilot]

Or... I have made this to complicated (common) and a date+time format would work! I am open to either. Thanks!

 

[auto.pilot]

How about

AvailableDates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		●	Available
3		Date	9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM		EST
4	Thursday	06/07/2023	x			x			x				● Thursday 7/6 @ 9:00 AM EST
5	Friday	07/07/2023		x			x						● Thursday 7/6 @ 12:00 PM EST
6	Saturday	08/07/2023											● Thursday 7/6 @ 3:00 PM EST
7	Sunday	09/07/2023											● Friday 7/7 @ 10:00 AM EST
8	Monday	10/07/2023			x					x			● Friday 7/7 @ 1:00 PM EST
9	Tuesday	11/07/2023	x					x					● Monday 7/10 @ 11:00 AM EST
10	Wednesday	12/07/2023		x									● Monday 7/10 @ 4:00 PM EST
11	Thursday	13/07/2023											● Tuesday 7/11 @ 9:00 AM EST
12	Friday	14/07/2023											● Tuesday 7/11 @ 2:00 PM EST
13	Saturday	15/07/2023											● Wednesday 7/12 @ 10:00 AM EST
14	Sunday	16/07/2023
15	Monday	17/07/2023
16	Tuesday	18/07/2023
17	Wednesday	19/07/2023
18	Thursday	20/07/2023
19	Friday	21/07/2023
20	Saturday	22/07/2023
21	Sunday	23/07/2023
22	Monday	24/07/2023
23	Tuesday	25/07/2023
24	Wednesday	26/07/2023
25	Thursday	27/07/2023
26	Friday	28/07/2023
27	Saturday	29/07/2023
28	Sunday	30/07/2023
29	Monday	31/07/2023
Sheet1

Cell Formulas
Range	Formula
A4:A29	A4	=VLOOKUP(WEEKDAY(B4),$P$5:$Q$11,2,FALSE)
B4	B4	=TODAY()
B5:B29	B5	=B4+1
M4:M13	M4	=LET(d,TOCOL(IF(C4:K160="x",B2&" "&A4:A160&" "&TEXT(B4:B160,"m/d")&" @ ",1/0),2)&TOCOL(IF(C4:K160="x",C3:K3&" "&M3,1/0),2),FILTER(d,NOT(ISNA(d))))
Dynamic array formulas.

I will give this a try. Thanks!

 

[auto.pilot]

This solution is extraordinary, like I'm watching the Excel Olympics. I never in my dreams thought that this could be accomplished with formulas.

fluffismyhero

Jim

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[auto.pilot]

For anyone who may search and find this post at a later date, I made one small modification to Fluff's excellent formula. When I copied and pasted the original formula into another sheet, the time formats did not display correctly. Here's the updated formula, pasted here as a picture so that I could identify the updated section in red font.