COUNT UNIQUE MONTHS in multiple date ranges

[mollys]

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]

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
	D	E	F	G
1	Start	End	Start	End
2	02-Apr-11	15-Apr-11	21-Apr-11	05-Jun-11
3	02-Apr-11	23-Jun-11	05-May-11	07-Jul-11
Count Months

 

[mollys]

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]

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
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start	End	Start	End	Start	End	Start	End	Start	End	Start	End		Months
2	15-Jan-11	15-Feb-11	02-Mar-11	12-Mar-11										3
3	10-Jan-11	20-Jan-11	01-Apr-11	05-Apr-11	01-Jul-11	12-Jul-11								3
4														0
5	02-Apr-11	15-Apr-11	21-Apr-11	05-Jun-11										3
6	01-Apr-11	02-Apr-11	03-Apr-11	04-Apr-11	04-Apr-11	06-Apr-11								1
7	01-Nov-08	31-Jan-09	01-Feb-09	05-May-10	06-Jun-10	08-Jun-10	12-Jun-10	28-Jul-10	29-Aug-10	01-Sep-10	18-Sep-10	15-Oct-10		24
Count Months

 

[mollys]

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!!


Molly

 

[Peter_SSs]

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!!


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.