COUNT UNIQUE MONTHS in multiple date ranges

mollys

New Member
Joined
Oct 14, 2009
Messages
14
I have been struggling with this for HOURS - I'm hoping the geniuses here can help.
In one worksheet I have a chart that looks like this:


Each row is unique and can have anywhere from one set of start-end dates, to six (as seen in the columns)

I need to COUNT the unique months seen in the sets of start-end dates.
In the example above, both rows would return 3.

Ideally I want to be able to put this formula into a vlookup on another tab. However, if I can't, I'm willing to make another column in this existing sheet in order to get at this data.

Thanks in advance for any advice!!!!

Molly
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,240
Office Version
365
Platform
Windows
Molly

You will get many more potential helpers if you post small screen shots directly in your post rather than linking to an image like that. Two main reasons ..

1. Cannot copy your sample from that screen shot and most helpers don't much like spending their time typing sample data. Copy/Paste is much more enticing. :)

2. Many people just won't bother even following your link to another site.

My signature block contains 3 methods of posting small screen shots. I've used one of them below. Data can be copied directly from such a screen shot.

Would data like either of these rows be possible?

If so, what results would you expect and why?

Excel Workbook
DEFG
1StartEndStartEnd
202-Apr-1115-Apr-1121-Apr-1105-Jun-11
302-Apr-1123-Jun-1105-May-1107-Jul-11
Count Months
 

mollys

New Member
Joined
Oct 14, 2009
Messages
14
Thank you Peter!
Apologies for the screenshot - I will create a table next time for sure.

Regarding your question:
Row 2 could occur. In this case, I would expect the result to be 3.

Row 3 would never occur as all dates are listed sequentially. A subsequent start date will NEVER be earlier than its preceding end date.
<table style="font-family: Arial,Arial; font-size: 8pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 21px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="font-size: 10pt; text-align: right;">Start</td><td style="font-size: 10pt; text-align: right;">End</td><td style="font-size: 10pt; text-align: right;">Start</td><td style="font-size: 10pt; text-align: right;">End</td></tr><tr style="height: 21px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="font-size: 10pt; text-align: right;">02-Apr-11</td><td style="font-size: 10pt; text-align: right;">15-Apr-11</td><td style="font-size: 10pt; text-align: right;">21-Apr-11</td><td style="font-size: 10pt; text-align: right;">05-Jun-11</td></tr><tr style="height: 21px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="font-size: 10pt; text-align: right;">02-Apr-11</td><td style="font-size: 10pt; text-align: right;">23-Jun-11</td><td style="font-size: 10pt; text-align: right;">05-May-11</td><td style="font-size: 10pt; text-align: right;">07-Jul-11</td></tr></tbody></table>

Thank you!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,240
Office Version
365
Platform
Windows
That may be possible with native Excel formulas but I cannot see a sensible way at this stage. Some of the board's helpers are experts with dates (I'm certainly not) and they may be able to add more information about the possibility of doing this with standard formulas if my suggestion is not suitable for you.

This is a UDF (user-defined function). To implement, with the relevant workbook open and active ..

1. Alt+F11 to open the VBA window.

2. Use the VBA menus to Insert|Module.

3. Copy the code below and Paste into the main right hand pane that opened at step 2.

4. Close the VBA window.

5. Use as a formula in the sheet as shown in the screen shot below.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> CountMonths(R <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> YrMnS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, YrMnE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, YrMnTmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Mnths <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Counter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> R.Rows.Count = 1 And R.Columns.Count Mod 2 = 0 <SPAN style="color:#00007F">Then</SPAN><br>        c = 2<br>        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> c <= R.Columns.Count<br>            <SPAN style="color:#00007F">If</SPAN> R.Cells(1, c).Value <> vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                d = R.Cells(1, c).Value<br>                YrMnE = Year(d) * 12 + Month(d)<br>                d = R.Cells(1, c - 1).Value<br>                YrMnS = Year(d) * 12 + Month(d)<br>                Mnths = YrMnE - YrMnS + 1<br>                Counter = Counter + Mnths - IIf(YrMnS = YrMnTmp, 1, 0)<br>                YrMnTmp = YrMnE<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            c = c + 2<br>        <SPAN style="color:#00007F">Loop</SPAN><br>        CountMonths = Counter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>


Excel Workbook
DEFGHIJKLMNOPQ
1StartEndStartEndStartEndStartEndStartEndStartEndMonths
215-Jan-1115-Feb-1102-Mar-1112-Mar-11
310-Jan-1120-Jan-1101-Apr-1105-Apr-1101-Jul-1112-Jul-113
40
502-Apr-1115-Apr-1121-Apr-1105-Jun-113
601-Apr-1102-Apr-1103-Apr-1104-Apr-1104-Apr-1106-Apr-111
701-Nov-0831-Jan-0901-Feb-0905-May-1006-Jun-1008-Jun-1012-Jun-1028-Jul-1029-Aug-1001-Sep-1018-Sep-1015-Oct-1024
Count Months
 

mollys

New Member
Joined
Oct 14, 2009
Messages
14
Peter -
This is truly remarkable! Thank you so so much for your advice and help! I only hope I'll be able to return the favor is some way!!

:biggrin:
Molly
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,240
Office Version
365
Platform
Windows
Peter -
This is truly remarkable! Thank you so so much for your advice and help! I only hope I'll be able to return the favor is some way!!

:biggrin:
Molly
Glad it's what you wanted, and thanks for the feedback. You don't need to return a favour to me specifically, but don't be afraid to try to answer somebody else's question if you see one you think you can have a go at. :)
 

Forum statistics

Threads
1,082,017
Messages
5,362,695
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top