Forum Discussion

J-Des000's avatar
J-Des000
Copper Contributor
Jun 11, 2024

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.


 

 

  • djclements's avatar
    djclements
    Bronze Contributor

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

    • J-Des000's avatar
      J-Des000
      Copper Contributor
      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!
  • 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.

     

  • J-Des000's avatar
    J-Des000
    Copper Contributor
    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.
    • djclements's avatar
      djclements
      Bronze Contributor

      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!

      • J-Des000's avatar
        J-Des000
        Copper Contributor

        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?

Resources