SOLVED

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

Copper Contributor

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

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.

 

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

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.

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

@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

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

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

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

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

@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

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

View solution in original post