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

 

[Special-K99]

TEXTJOIN if you have the correct version of Excel or VBA only I would suspect

 

[tyija1995]

Hi btardo01,

I have wrote a code that may do what you have requested, please check!

Sub Months()
    Dim j As Integer
    Dim k As Integer
    Dim monthNo As Integer
    Dim lastRowB As Integer
    Dim dates As String
    
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    
    lastRowB = Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To lastRowB
        dates = Cells(j, 2).Value
        monthNo = Left(dates, WorksheetFunction.Search("/", dates) - 1)
        Cells(j, 3).Value = monthNo
        For k = 1 To monthNo
            Cells(j, 4).Value = Cells(j, 4).Value & k & ","
        Next k
        Cells(j, 4).Value = Left(Cells(j, 4), Len(Cells(j, 4).Value) - 1)
        Cells(j, 3).Value = Cells(j, 4).Value
    Next j
        Columns("D:D").Select
        Selection.Delete Shift:=xlToLeft
End Sub

It starts scanning your data from B2 downwards and returns consecutive numbers in C2 up unto the month.
So 11/19/2018 in B2 would return 1,2,3,4,5,6,7,8,9,10,11 in cell C2.

 

[Fluff]

If you don't have textjoin you could use this UDF

Function btardo01(cl As Range) As String
   Dim i As Long
   btardo01 = "{"
   For i = 1 To month(cl.Value) - 1
      btardo01 = btardo01 & i & ","
   Next i
   btardo01 = btardo01 & i & "}"
End Function

Used like

Excel 2013/2016
	B	C
2	01/03/2018	{1,2,3}
3	01/01/2018	{1}
4	01/12/2018	{1,2,3,4,5,6,7,8,9,10,11,12}
Template

Cell Formulas
Range	Formula
C2	=btardo01(B2)

 

[btardo01]

Thank you all for the responses. I do have textjoin. So, I gave it a shot and it seemed to work. However, I am struggling with the second part. If I have a date value in cell B2, how to I get ever month in that same calendar year to be in the list For example if B2 is 3/1/2018, if would return {1,2,3}. If B2 is 6/15/2018, if would return {1,2,3,4,5,6}. I am hoping this piece can be accomplished without the array. I'll try the VBA piece as well but would be great to have as a formula too.

thanks again

 

[tyija1995]

In the VBA code I wrote it doesn't include the braces { } but that can easily be amended by adding the following line of code to the procedure:

Cells(j, 3).Value = "{" & Cells(j, 3).Value & "}"

Copy and paste that above the "Next j" in the code. That will then return {1,2,3,4,5} in cell C2 if B2 is 5/15/2018 for example.

 

[Marcelo Branco]

A possible solution using formulas

	A​	B​
1​	Date​	Result​
2​	03/01/2018​	{1,2,3}​
3​	01/01/2018​	{1}​
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}​

Formula in B2 copied down
="{"&LEFT("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)&"}"

M.

 

[btardo01]

A possible solution using formulas

	A​	B​
1​	Date​	Result​
2​	03/01/2018​	{1,2,3}​
3​	01/01/2018​	{1}​
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}​

<tbody>
</tbody>

Formula in B2 copied down
="{"&LEFT("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)&"}"

M.

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.

 

[btardo01]

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.

 

[Fluff]

With the UDF you can use this for the excluded values

Function Exclude(cl As Range) As String
   Dim i As Long
   If month(cl.Value) = 12 Then Exit Function
   Exclude = "{"
   For i = month(cl.Value) + 1 To 11
      Exclude = Exclude & i & ","
   Next i
   Exclude = Exclude & i & "}"
End Function

Excel 2013/2016
	B	C	D
2	01/03/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	01/12/2018	{1,2,3,4,5,6,7,8,9,10,11,12}
5	01/11/2018	{1,2,3,4,5,6,7,8,9,10,11}	{12}
Template

Cell Formulas
Range	Formula
C2	=btardo01(B2)
D2	=Exclude(B2)