need help with new sequence function

%3CLINGO-SUB%20id%3D%22lingo-sub-1339046%22%20slang%3D%22en-US%22%3Eneed%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339046%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20am%20tryig%20to%20configure%20a%20document%20and%20need%20help%20with%20the%20new%202019%20auto%20sequence.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20i%20would%20have%20a%20date%20lets%20say%3C%2FP%3E%3CP%3E4%2F26%2F2020%20in%20cell%20a%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20cell%20b%20i%20would%20be%20putting%20the%20amount%20of%20months%20lets%20say%2030%3C%2FP%3E%3CP%3Ei%20would%20like%20to%20auto%20generate%20the%20next%2030%20months%20with%20the%20same%20day%20and%20switch%20the%20year%20and%20month.%20for%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ea1%20-%204%2F26%2F2020%20(beginning%20date)%3C%2FP%3E%3CP%3Eb1%20-%2030%20(months)%3C%2FP%3E%3CP%3Ec1%20-%205%2F26%2F2020%20(auto%20sequence%20function)%3C%2FP%3E%3CP%3Ed1%20-%206%2F26%2F2020%20(populated%20automatically)%3C%2FP%3E%3CP%3Ee1%20-%207%2F26%2F2020%20(populated%20automatically)%3C%2FP%3E%3CP%3Ef1%20-%208%2F26%2F2020%20(populated%20automatically)%3C%2FP%3E%3CP%3Eetc.%20etc.%20all%20the%20way%20to%2030%20months%20after%20the%20beginning%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%20if%20you%20can%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1339046%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339068%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637559%22%20target%3D%22_blank%22%3E%40knowu22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DEDATE(A1%2CSEQUENCE(1%2CB1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20apply%20date%20format%20to%20the%20result%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339073%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637559%22%20target%3D%22_blank%22%3E%40knowu22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20shortest%20way%20would%20be%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20EDATE(%20start%2C%20SEQUENCE(N%2C1%2C0)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhere%20%3CSTRONG%3E'start'%3C%2FSTRONG%3E%20refers%20to%20the%20start%20date%20and%20%3CSTRONG%3E'N'%3C%2FSTRONG%3E%20is%20the%20number%20of%20months%20in%20the%20output%20list.%26nbsp%3B%20One%20could%20also%20use%20the%20%3CSTRONG%3EDATE%3C%2FSTRONG%3E%20function%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20DATE(%20YEAR(start)%2C%20SEQUENCE(N%2C1%2CMONTH(start))%2C%20DAY(start)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339169%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3Bgreat%20!!!%20that%20is%20what%20i%20was%20looking%20for%20to%20the%20T.%20Thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%20here%20is%20another%20one%20for%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20i%20wanted%20to%20separate%20it%20into%202%20different%20columns%20like%20if%20if%20had%2010%20and%20wanted%205%20of%20those%20dates%20in%201%20column%20and%205%20in%20the%20second%20column%20how%20would%20i%20do%20that%20or%20is%20that%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20to%20do%20what%20we%20just%20did%20with%20the%20auto%20sequence%20function%20but%20in%20an%20excel%20version%20that%20doesn't%20yet%20support%20auto%20sequence%3F%20lets%20say%20like%20excel%202007%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339181%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339181%22%20slang%3D%22en-US%22%3Ethank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1339577%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1339577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F637559%22%20target%3D%22_blank%22%3E%40knowu22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20presentation%20of%20dates%20in%20separate%20columns%20could%20be%20achieved%20by%20building%20a%202D%20array%20for%20the%20criterion%2C%20%3CEM%3Ee.g.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20IF(%20RANDARRAY(N%2C1%2C0%2C1%2C1)%2C%20%7B1%2C0%7D%2C%20%7B0%2C1%7D%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eeither%20on%20the%20worksheet%20or%20within%20a%20defined%20name.%26nbsp%3B%20The%20worksheet%20formula%20would%20then%20be%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20IF(%20criterion%23%2C%20dateSequence%23%2C%20%22%22%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EAs%20for%20Office%202007%2C%20it%20is%20likely%20that%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20will%20have%20better%20advice%20to%20offer%20than%20I.%26nbsp%3B%20One%20option%20might%20be%20to%20define%20a%20name%20'%3CSTRONG%3Ek%3C%2FSTRONG%3E'%20that%20refers%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20ROW(INDIRECT(%221%3A%22%26amp%3BN))%20-%201%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ein%20which%20case%20the%20formula%20becomes%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20EDATE(%20start%2C%20k%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIt%20would%20need%20to%20be%20committed%20using%20CSE%20which%20makes%20the%20process%20somewhat%20inflexible%20and%20inconvenient.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1342324%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20new%20sequence%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20are%20truly%20an%20expert!!%20if%20you%20need%20help%20with%20any%20assignments%20you%20have%20i%20would%20love%20to%20be%20challenged%20with%20helping%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@knowu22 

That's

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

and apply date format to the result 

Highlighted

@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) )

Highlighted

@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 

Highlighted
thank you
Highlighted

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

Highlighted

@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