Count unique months between date ranges

[Levigate]

I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?

Thanks!

 

[sanrv1f]

Hello and welcome to the board,

Could you post some sample data and the expected results, as it would be helpful to understand the problem, better

see my signature for help on posting the data on board, alternatively, you can just copy a range from excel and paste to your post

 

[wsjackman]

One way to do it is to use two helper columns (HC,HD


assuming your start dates are in column A and the finish dates are in column B

in column HC make a list of all months between the earliest month in A and last month in B

In HD1,
=if(sumproduct((HC1>=A1:A1000)*(HC1<=B1:B1000)),1,0)
copy and paste down

=sum(HD:HD) will give you the number of unique months.

(adjust all ranges as needed)

 

[Levigate]

Hi Shankar and jackman,

Sorry for not giving more details. I have attached a sample sheet so you could get an understanding of what I'm after. (attachment feature is disabled, so i'm going to upload to rapidshare)

URL>>
http://rapidshare.com/files/291964381/Unique_months_Example.xls

Notice that the count of months includes the start and end months in the count.
Do let me know if there are any questions.

 

[wsjackman]

the file would not download for me.

 

[Gerald Higgins]

I'm not allowed to access that site from work.

Assume start date is in A1, and end date is in B1

=((year(b1)-year(a1))*12)+month(b1)-month(a1)

This will return 1 for start date 31st Jan 2009, end date 1st Feb 2009.

If you want the result of this to be 2, then use

=((year(b1)-year(a1))*12)+month(b1)-month(a1)+1

 

[Levigate]

I hope this works...

Excel Workbook
	B	C	D	E	F	G	H
1
2		Activity A	Activity B	Activity C	Activity D
3	Start Date	Oct-07	Jul-08	Jan-09	May-09
4	End Date	Jun-08	Feb-09	Apr-09	Jun-09
5		9	8	4	2	23
6
7	Total work time:	21
8	(unique months)
9
10	Notes:	The activities list is not fixed - could be as few as 4 and as high as 50The date format is going to be mmm-yy
11
12
13
Sheet1

The cell C7 does not have a formula. The months were manually calculated.

Still has some alignment issues. But i think it gives an idea of what is in the spreadsheet.

 

[Levigate]

Hi Gerald,

What i want is a sum of all the unique months.
The formula =DATEDIF(C3,C4,"M")+1 did the same thing as
=((YEAR(C4)-YEAR(C3))*12)+MONTH(C4)-MONTH(C3)+1



How do we get the unique months from this now?

 

[Colin Legg]

This might work:

Excel Workbook
	B	C	D	E	F	G
2		Activity A	Activity B	Activity C	Activity D
3	Start Date	Oct-07	Jul-08	Jan-09	May-09
4	End Date	Jun-08	Feb-09	Apr-09	Jun-09
5		0	0	0	0	<-- This checks for gaps between activities.
6
7	Total Work Time	21
Sheet1

Excel 2002

Cell Formulas
Range	Formula
C5	=IF(D3>C4,DATEDIF(C4,D3,"m")-1,0)
C7	=1+DATEDIF(MIN(C3:F3),MAX(C4:F4),"m")-SUM(C5:F5)
D5	=IF(E3>D4,DATEDIF(D4,E3,"m")-1,0)
E5	=IF(F3>E4,DATEDIF(E4,F3,"m")-1,0)
F5	=IF(G3>F4,DATEDIF(F4,G3,"m")-1,0)

 

[Levigate]

Hi Colin,

That's an interesting take on the logic. I have tried to replicate the same and i get 20, instead of 21. I'll also see if there could be potential bugs (aside from the fact that the data has to be organized from the oldest to the latest and should not be entered in random fashion).

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 106px;"><col style="width: 70px;"><col style="width: 69px;"><col style="width: 72px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 384px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(0, 0, 0); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;"> </td><td style="background-color: rgb(0, 0, 0); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">Activity A</td><td style="background-color: rgb(0, 0, 0); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">Activity B</td><td style="background-color: rgb(0, 0, 0); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">Activity C</td><td style="background-color: rgb(0, 0, 0); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">Activity D</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">Start Date</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Oct-07</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Jul-08</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Jan-09</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">May-09</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt;">End Date</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Jun-08</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Feb-09</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Apr-09</td><td style="background-color: rgb(51, 102, 255); color: rgb(255, 255, 255); font-weight: bold; font-family: Verdana; font-size: 8pt; text-align: center;">Jun-09</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td> </td><td style="text-align: right;">0</td><td style="text-align: right;">0</td><td style="text-align: right;">0</td><td style="text-align: right;">0</td><td style="font-weight: bold; text-align: right;">0</td><td><-- this is the total duration (but has over-lapping months as well</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="font-weight: bold;">Total work time:</td><td style="text-align: right;">20</td><td colspan="3" style="text-align: left;"><-- this is the expected result</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>(unique months)</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>Notes:</td><td colspan="4" rowspan="3" style="text-align: left;">The activities list is not fixed - could be as few as 4 and as high as 50
Date format: mmm-yy</td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td> </td><td> </td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C5</td><td>=IF(D3>C4,DATEDIF(C4,D3,"m")-1,0)</td></tr><tr><td>D5</td><td>=IF(E3>D4,DATEDIF(D4,E3,"m")-1,0)</td></tr><tr><td>E5</td><td>=IF(F3>E4,DATEDIF(E4,F3,"m")-1,0)</td></tr><tr><td>F5</td><td>=IF(G3>F4,DATEDIF(F4,G3,"m")-1,0)</td></tr><tr><td>G5</td><td>=SUM(C5:F5)</td></tr><tr><td>C7</td><td>=1+DATEDIF(MIN(C3:F3),MAX(C4:F4),"m")-SUM(C5:F5)</td></tr></tbody></table></td></tr></tbody></table>