# Return common-sperated list of months between two dates

#### btardo01

##### Board Regular
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
TEXTJOIN if you have the correct version of Excel or VBA only I would suspect

Hi btardo01,

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

Code:
``````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.

If you don't have textjoin you could use this UDF
Code:
``````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
BC
201/03/2018{1,2,3}
301/01/2018{1}
401/12/2018{1,2,3,4,5,6,7,8,9,10,11,12}
Template
Cell Formulas
RangeFormula
C2=btardo01(B2)

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

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:
Code:
``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.

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.

Last edited:
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.

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

Code:
``=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.

With the UDF you can use this for the excluded values
Code:
``````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
BCD
201/03/2018{1,2,3}{4,5,6,7,8,9,10,11,12}
301/01/2018{1}{2,3,4,5,6,7,8,9,10,11,12}
401/12/2018{1,2,3,4,5,6,7,8,9,10,11,12}
501/11/2018{1,2,3,4,5,6,7,8,9,10,11}{12}
Template
Cell Formulas
RangeFormula
C2=btardo01(B2)
D2=Exclude(B2)

Replies
7
Views
332
Replies
3
Views
201
Replies
9
Views
338
Replies
1
Views
715
Replies
11
Views
1K

1,221,525
Messages
6,160,328
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back