Forum Discussion
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.
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...
- djclementsBronze 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-Des000Copper Contributordjclements 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!
- djclementsBronze Contributor
J-Des000 No worries, I enjoyed the challenge. 🙂 You're welcome!
- Martin_AngostoIron Contributor
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-Des000Copper ContributorThank 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.
- djclementsBronze 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-Des000Copper 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?