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