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

[J-Des000]

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.

[#2]

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 @PeterBartholomew1 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.

[#3]

@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...

[#4]

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.

[#5]

@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!

[#6]

@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?

[#7 · Accepted solution]

@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...

[#8]

@djclements 

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...

[#9]

@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). 

[#10]

@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!

[#11]

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