3

Is it possible to generate an array of numbers between two provided numbers eg =sequence(1,10) results in {1,2,3,4,5,6,7,8,9,10}

My end goals is to generate a list of days between two dates, but have tried to generalise the question.

I thought about using =TREND, but I couldn't figure it out.

5
=arrayformula(row(1:10))

returns a list of numbers 1 through 10, as a column. Can be made a row with transpose.

A dynamic version, where the upper bound is taken from cell C1:

=arrayformula(row(indirect("1:" & C1)))
3
  • Thanks, That works well, I thought from this I'd be able to get a series of dates dynamically, However, I've now got stuck on replacing the 10 with days(today(), $B$1) Feb 9 '18 at 3:05
  • 1
    See the edit. Reference C1 could be any formula.
    – user135384
    Feb 9 '18 at 3:42
  • champion, cheers Feb 9 '18 at 3:49
0

You can also do same using DATE function, this gives you ability to generate months or years.

Pretending start date is written in A2 and end date is written in B2. This formula will generate a list of dates between (inluding start, excluding end).

=ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2), DAY(A2) + SEQUENCE(DATEDIF(A2,B2,"D"), 1, 0, 1)))

Few explanations:

DATE function has ability to move over month if days is over last day of that month of that year. Same goes for months.

DATEDIF(A2,B2,"D") gives you amount of days between the dates. You can + 1 if you want to include end date.

SEQUENCE(DATEDIF(A2,B2,"D"), 1, 0, 1) generates number from 0 to amount of days generated above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.