Returning a list of dates dependent on type of day

[penpro]

Hi there helpful people!

I have a sheet with a list of dates for 2023 in column A, and I have used the weekday function to label the days as 1 - 7 in column B, plus an xlookup table to have a column C where each day is labelled as a Monday / Tuesday / Wednesday / Thursday / Friday / Weekend / Holiday.

In another sheet in the workbook, I want to return across a row the list of dates for 2023 that are labelled as Monday / Tuesday / Wednesday / Thursday /Friday only.

Any tips on an efficient way to do this please?

Many thanks for any help you can give me.

 

[AhoyNC]

Maybe something like this. Adjust ranges to match your data.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	Day
2	01/01/23	1	Holiday		01/03/23	01/04/23	01/05/23	01/06/23	01/09/23	01/10/23	01/11/23	01/12/23	01/13/23	01/16/23	01/17/23	01/18/23
3	01/02/23	2	Holiday
4	01/03/23	3	Tuesday
5	01/04/23	4	Wednesday
6	01/05/23	5	Thursday
7	01/06/23	6	Friday
8	01/07/23	7	Saturday
9	01/08/23	1	Sunday
10	01/09/23	2	Monday
11	01/10/23	3	Tuesday
12	01/11/23	4	Wednesday
13	01/12/23	5	Thursday
14	01/13/23	6	Friday
15	01/14/23	7	Saturday
16	01/15/23	1	Sunday
17	01/16/23	2	Monday
18	01/17/23	3	Tuesday
19	01/18/23	4	Wednesday
Sheet1

Cell Formulas
Range	Formula
E2:P2	E2	=TRANSPOSE(FILTER($A$2:$A$19,($C$2:$C$19<>"Holiday")*($C$2:$C$19<>"Saturday")*($C$2:$C$19<>"Sunday"),""))
Dynamic array formulas.

 

[penpro]

Genius, thanks so much! I'm loving learning how to do more with Excel

 

[AhoyNC]

You're welcome. Thanks for the feedback.