I am trying to create a list of ordered date for the 3rd of the month, 16 of the month, and every Friday.
It looks like this
B2 - 2002/1/3
B3 - 2002/1/16
B4 - 2020/9/11
=TRANSPOSE(EDATE(B2,SEQUENCE(36,1,0)))
=TRANSPOSE(EDATE(B3,SEQUENCE(36,1,0,1)))
=TRANSPOSE((SEQUENCE(144,1,B4,7)))
I would like it all to be in on row with unique values only. I tried to use LET but I can't figure our how to have them merged into one list.
Thanks!
CodePudding user response:
Using LET and CHOOSE:
=LET(a,EDATE(B2,SEQUENCE(1,36,0)),
b,EDATE(B3,SEQUENCE(1,72,-36,1)),
c,SEQUENCE(1,144 72,B4-(72*7),7),
d,SEQUENCE(1,144 36 36),
SORT(CHOOSE(IF(d<37,1,IF(d<73,2,3)),a,b,c),1,1,TRUE))
There is a new function currently on available to Office 365 insiders beta channel HSTACK that would simplify this:
=LET(a,EDATE(B2,SEQUENCE(1,36,0)),
b,EDATE(B3,SEQUENCE(1,36,0,1)),
c,SEQUENCE(1,144,B4,7),
SORT(HSTACK(a,b,c),1,1,TRUE))