Return common-sperated list of months between two dates

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
TEXTJOIN if you have the correct version of Excel or VBA only I would suspect
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,436
Office Version
  1. 365
Platform
  1. Windows
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)
 

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
165
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,035
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Oct 6, 2009
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,436
Office Version
  1. 365
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,447
Members
430,549
Latest member
jayjay2022

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?

Disable AdBlock

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
Top