Apr 26 2020 11:33 AM
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.
Apr 26 2020 11:55 AM
Apr 26 2020 11:59 AM
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) )
Apr 26 2020 01:17 PM
@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
Apr 26 2020 10:54 PM - edited Apr 27 2020 01:47 AM
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.
Apr 27 2020 08:36 PM
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