Expand Start/End date into single column list of dates.

[HereToday]

First post on this forum. Running M365 (Excel Mac).

I've got a workaround for this but I'm looking for a more elegant solution.

In essence I'd like to create a non-VBA function that expands a start/end date range into series of dates, starting with first start date, listing all dates between first start and end date, then iterating to the next start date and so on, until the end of a table range is reached.

ATM, I've got a work around that uses 3 hidden helper columns, which works but is quite clunky.

Column N: I list all dates in a year (static sequence of numbers).
Column O: use the following function to compare Column 1 dates to original table start/end dates: =IF(SUM((N14 >= $C$4:$C$6)*(N14 <= $D$4:$D$6)),N14,"")
Where C is start date, D is end Date, N is Column 1.
Column P: using the Unique function to pull column O into a single list, without spaces or gaps.

So whilst i have a "method" of achieving what's shown in the screen shot it's far from elegant. Any ideas about a better way of achieving the same thing?



Is there any method of achieving the same result in one "hit"?

 

[Fluff]

How about

Fluff.xlsm
	C	D	E	F
1
2	12/01/2022	26/01/2022		12/01/2022
3	28/01/2022	11/02/2022		13/01/2022
4	15/02/2022	01/03/2022		14/01/2022
5				15/01/2022
6				16/01/2022
7				17/01/2022
8				18/01/2022
9				19/01/2022
10				20/01/2022
11				21/01/2022
12				22/01/2022
13				23/01/2022
14				24/01/2022
15				25/01/2022
16				26/01/2022
17				28/01/2022
18				29/01/2022
19				30/01/2022
20				31/01/2022
21				01/02/2022
22				02/02/2022
23				03/02/2022
24				04/02/2022
25				05/02/2022
26				06/02/2022
27				07/02/2022
28				08/02/2022
29				09/02/2022
30				10/02/2022
31				11/02/2022
32				15/02/2022
33				16/02/2022
34				17/02/2022
35				18/02/2022
36				19/02/2022
37				20/02/2022
38				21/02/2022
39				22/02/2022
40				23/02/2022
41				24/02/2022
42				25/02/2022
43				26/02/2022
44				27/02/2022
45				28/02/2022
46				01/03/2022
47
Report

Cell Formulas
Range	Formula
F2:F46	F2	=LET(a,C2:C4,b,D2:D4,s,SEQUENCE(365,,C2),FILTER(s,BYROW(s,LAMBDA(br,SUM(--(br>=a),--(br<=b))>ROWS(a)))))
Dynamic array formulas.

 

[ISY]

Hi

Excel 365 Beta

=LET(K,C2:C4-B2:B4,x,MAX(K),y,SEQUENCE(,x),HSTACK(TOCOL(IF(y<=K,A2:A4,#N/A),3),TOCOL(IF(y<=K,y+B2:B4-1,#N/A),3)))

 

[Fluff]

@ISY when posting solutions that are only available to people on the Beta channel, please make that clear.

 

[HereToday]

How about

Fluff.xlsm
	C	D	E	F
1
2	12/01/2022	26/01/2022		12/01/2022
3	28/01/2022	11/02/2022		13/01/2022
4	15/02/2022	01/03/2022		14/01/2022
5				15/01/2022
6				16/01/2022
7				17/01/2022
8				18/01/2022
9				19/01/2022
10				20/01/2022
11				21/01/2022
12				22/01/2022
13				23/01/2022
14				24/01/2022
15				25/01/2022
16				26/01/2022
17				28/01/2022
18				29/01/2022
19				30/01/2022
20				31/01/2022
21				01/02/2022
22				02/02/2022
23				03/02/2022
24				04/02/2022
25				05/02/2022
26				06/02/2022
27				07/02/2022
28				08/02/2022
29				09/02/2022
30				10/02/2022
31				11/02/2022
32				15/02/2022
33				16/02/2022
34				17/02/2022
35				18/02/2022
36				19/02/2022
37				20/02/2022
38				21/02/2022
39				22/02/2022
40				23/02/2022
41				24/02/2022
42				25/02/2022
43				26/02/2022
44				27/02/2022
45				28/02/2022
46				01/03/2022
47
Report

Cell Formulas
Range	Formula
F2:F46	F2	=LET(a,C2:C4,b,D2:D4,s,SEQUENCE(365,,C2),FILTER(s,BYROW(s,LAMBDA(br,SUM(--(br>=a),--(br<=b))>ROWS(a)))))
Dynamic array formulas.

Well that is just neat as ninepence! It's going to take me a while to work through the formula to understand it but many, many thanks!

 

[Fluff]

Glad to help & thanks for the feedback.