SOLVED

# Generating a list of Dates using nested array inside SEQUENCE function?

Copper Contributor

# Generating a list of Dates using nested array inside SEQUENCE function?

Hello,

I am trying to generate a list of dates with different intervals using an array inside of a single SEQUENCE function. How do I do this? Cell references are in bracket below and the results I'm looking for.

Ex:

Start Date                   End Date                     Day Interval

(A1) 2024-02-15        (B1) 2024-03-07         (C1) 7
(A2) 2024-02-23        (B2) 2024-03-22         (C2) 14

Looking to generate

2024-02-15

2024-02-22

2024-02-29
2024-03-07
2024-02-23
2024-03-08

2024-03-22

Basically it generates a list of values going through each start date in the array (A1:A2) and their respective interval (C1:C2). I am trying to use one instance of the SEQUENCE function to do this nesting arrays inside instead of using a SEQUENCE function for each start date. Any ideas? Thank you in advance.

10 Replies

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

Hi @J-Des000 ,

I can propose a suggestion for generating the desired sequence for an individual start/end date. Attaching a sample document.

``=LET(startDate,A1,endDate,B1,step,C1,SEQUENCE((endDate-startDate+step)/step,1,startDate,step))``

As per generating this output for both rows of dates, I am afraid the only solution that has come to my mind would unfortunately result into an attempt to generate an array of arrays, which Excel does not support (as @Peter Bartholomew had taught me not so long ago).

Would love to see how he would suggest a solution for this. Probably something can be done with MAP() function or other similar paths, which I am not familiar with yet.

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@J-Des000 My preferred method for situations like this is the TOCOL / IFS / SEQUENCE method:

``````=LET(
start, A1:A2,
end, B1:B2,
num, C1:C2,
low, MIN(start),
seq, SEQUENCE(, MAX(end) - low + 1, low),
TOCOL(IFS(NOT(MOD(seq - start, num)) * (start <= seq) * (end >= seq), seq), 2)
)``````

Alternatively, the REDUCE / VSTACK method could be used, but it will not perform as well on larger datasets:

``````=LET(
arr, A1:C2,
DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(p,n, LET(
start, INDEX(arr, n, 1),
end, INDEX(arr, n, 2),
step, INDEX(arr, n, 3),
VSTACK(p, SEQUENCE( (end - start) / step + 1,, start, step))))), 1)
)``````

See attached...

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

Thank you all. @djclements the TOCOL / IFS / SEQUENCE method works perfectly for what I need. Unfortunately the REDUCE / VSTACK method crashes my workbook whenever I try adding a new row.

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@J-Des000 Fair enough. The TOCOL / IFS / SEQUENCE method is what I would recommend anyways, so I'm glad it worked out for you. Just ignore the other method... Cheers!

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@djclements Just a quick question - if I needed to modify this formula for monthly increments (e.g 1, 2, etc instead (keeping the same day #) how would this be done?

best response confirmed by J-Des000 (Copper Contributor)
Solution

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@J-Des000 Please see the updated file below, which includes examples for days, months, and mixed intervals on separate worksheets. Hopefully that's what you meant...

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

Desperate I can't Like your solution 5 times nor mark it as best response. Hopefully @J-Des000 will find a minute to do it...

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@Lorenzo Thanks for the support! I hear yah, but I'm not holding my breath. It seems like the majority of users here don't follow-up by marking the "best response" of their choice. I gave up on trying to encourage people to do so after receiving this response from one user, lol. Still, the laid-back "discussion" format of this forum is much more enjoyable than the overly competitive nature of sites like Stack Overflow (the narcissism on display over there is absolutely unbearable).

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@djclements apologies for the late reply; I got caught up with a few things, but already confirmed your last message as the best response - works perfectly for what I need. Thanks again!

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@J-Des000 No worries, I enjoyed the challenge. :) You're welcome!

1 best response

Accepted Solutions
best response confirmed by J-Des000 (Copper Contributor)
Solution

# Re: Generating a list of Dates using nested array inside SEQUENCE function?

@J-Des000 Please see the updated file below, which includes examples for days, months, and mixed intervals on separate worksheets. Hopefully that's what you meant...