Return common-sperated list of months between two dates

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
160
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
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
46,639
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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01/03/2018</td><td style=";">{1,2,3}</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">01/01/2018</td><td style=";">{1}</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">01/12/2018</td><td style=";">{1,2,3,4,5,6,7,8,9,10,11,12}</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Template</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=btardo01(<font color="Blue">B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
160

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
766
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
16,393

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
160
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
160
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
46,639
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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01/03/2018</td><td style=";">{1,2,3}</td><td style=";">{4,5,6,7,8,9,10,11,12}</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">01/01/2018</td><td style=";">{1}</td><td style=";">{2,3,4,5,6,7,8,9,10,11,12}</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">01/12/2018</td><td style=";">{1,2,3,4,5,6,7,8,9,10,11,12}</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">01/11/2018</td><td style=";">{1,2,3,4,5,6,7,8,9,10,11}</td><td style=";">{12}</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Template</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=btardo01(<font color="Blue">B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=Exclude(<font color="Blue">B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top