need help with new sequence function

Copper Contributor

hello, 

 

i am tryig to configure a document and need help with the new 2019 auto sequence. 

 

so i would have a date lets say

4/26/2020 in cell a 

in cell b i would be putting the amount of months lets say 30

i would like to auto generate the next 30 months with the same day and switch the year and month. for example

 

a1 - 4/26/2020 (beginning date)

b1 - 30 (months)

c1 - 5/26/2020 (auto sequence function)

d1 - 6/26/2020 (populated automatically)

e1 - 7/26/2020 (populated automatically)

f1 - 8/26/2020 (populated automatically)

etc. etc. all the way to 30 months after the beginning date

 

thank you very much if you can help. 

 

 

6 Replies

@knowu22 

That's

=EDATE(A1,SEQUENCE(1,B1))

and apply date format to the result 

@knowu22 

The shortest way would be

= EDATE( start, SEQUENCE(N,1,0) )

where 'start' refers to the start date and 'N' is the number of months in the output list.  One could also use the DATE function

= DATE( YEAR(start), SEQUENCE(N,1,MONTH(start)), DAY(start) )

@Peter Bartholomew great !!! that is what i was looking for to the T. Thank you

 

also here is another one for you. 

 

if i wanted to separate it into 2 different columns like if if had 10 and wanted 5 of those dates in 1 column and 5 in the second column how would i do that or is that possible?

 

also, 

 

is there a way to do what we just did with the auto sequence function but in an excel version that doesn't yet support auto sequence? lets say like excel 2007 

thank you

@knowu22 

The presentation of dates in separate columns could be achieved by building a 2D array for the criterion, e.g.

= IF( RANDARRAY(N,1,0,1,1), {1,0}, {0,1} )

either on the worksheet or within a defined name.  The worksheet formula would then be

= IF( criterion#, dateSequence#, "" )

 

As for Office 2007, it is likely that @Sergei Baklan will have better advice to offer than I.  One option might be to define a name 'k' that refers to

= ROW(INDIRECT("1:"&N)) - 1

in which case the formula becomes

= EDATE( start, k )

It would need to be committed using CSE which makes the process somewhat inflexible and inconvenient.

@Peter Bartholomew 

 

thank you, 

 

you are truly an expert!! if you need help with any assignments you have i would love to be challenged with helping you. 

 

Thank you again