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>