Need formula to calculate days in each month across multiple Start and End dates

Another Senior Moment, I'm afraid!

Attached has four entries with Start and End dates, some of which span more than one month.

Can't remember formula to "break and combine" the days between the relevant months to create table as shown in Cols H - J

(E.g. Row 3 is 24 days between 14/02 - 09/03, 9 of which are in March. Row 5 then has 8 days between 14 - 21/03, so formula needs to identify the March total should be 17 days)

All suggestions, pointers and solutions received gratefully as ever.

Ochimus
.

Gfranco - sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

That's just to extract the days from each month

This formula in F2 copied down will give you the same results in your latest attachment, but with zeroes in place of blanks

=MAX(0,MIN(EOMONTH(F$1,0),D2)-MAX(F$1,C2)+1)

If you want the zeroes to look like blanks custom format column as 0;;


It is possible, though, to get the results you suggested in your first workbook, with a single formula and no intermediate calculations

Change the dates in H3 down so they are the 1st of each month (you can format as mmm-yy if you want to show Jan-17 etc.)

Now in I3 you can use this array formula

=SUM(IF($D$2:$D$5>=$H3,IF($C$2:$C$5<=EOMONTH($H3,0),IF($B$2:$B$5=I$2,IF($D$2:$D$5>EOMONTH($H3,0),EOMONTH($H3,0),$D$2:$D$5)-IF($C$2:$C$5>$H3,$C$2:$C$5,$H3)+1))))

confirm with CTRL+SHIFT+ENTER and copy across to J3 and down, see attached

If you just use this part....

=MIN(EOMONTH(F$1,0),D2)-MAX(F$1,C2)+1

You will get some negative results where the C2-D2 range doesn't intersect with that month at all, so MAX with zero means that all the negative results are converted to zeroes

Note: I edited my earlier post to include a solution which gives your original table without individual results for each row