Return common-sperated list of months between two dates

[btardo01]

Is it possible to return a list of common separate months?

For example, in Cell B2, I have the date 3/1/2018. In cell C2, can I return this: "{1,2,3}" because that is the list of months that occur from beginning of year to now. as another example, If cell b2 reads 6/15/2018 then cell c2 would read "{1,2,3,4,5,6}"

I'd like to do this without an array formula if possible.

thanks
Brian

 

[Marcelo Branco]

I really appreciate all the feedback. The formula worked perfectly. What is, in cell C1 I wanted to put the months not included in B1...for example {4,5,6,7,8,9,10,11,12}?

I am about to work on the macro solution as well to see if I can make that route work as well.

	A​	B​	C​
1​	Date​	Result1​	Result2​
2​	03/01/2018​	{1,2,3}​	{4,5,6,7,8,9,10,11,12}​
3​	01/01/2018​	{1}​	{2,3,4,5,6,7,8,9,10,11,12}​
4​	12/01/2018​	{1,2,3,4,5,6,7,8,9,10,11,12}​	{}​
5​	06/15/2018​	{1,2,3,4,5,6}​	{7,8,9,10,11,12}​

Formula in B2 copied down (see post 7)

Formula in C2 copied down
="{"&MID("1,2,3,4,5,6,7,8,9,10,11,12",SEARCH("|",SUBSTITUTE("1,2,3,4,5,6,7,8,9,10,11,12,",",","|",MONTH(A2)))+1,99)&"}"

M.

 

[btardo01]

Everything provide is awesome. IF anyone knows how I can simply reference B2 in place of the "{1,2,3}" in my getpivotdata then I would be greatly appreciated. That would solve a HUGE problem for me!

One more follow up. I am currently using this formula:

=sum(getpivotdata("[Measures].[Rev]",'TTM '!$A$5,"[Time].[Calendar]","[Time].[Calendar].[Year].&[2018].&["&{1,2,3}&"]",.............

Can I somehow replace the {1,2,3} and reference cell B2? I tried but it didn't like it.