Split Dates formula

[jamesa2487]

Hi

I have attached a document with 2 sheets

Sheet 1 has no formulas and is purely data that shows sick time between 2 dates there are multiple entries per cost centre, I have highlighted in yellow on sheet one the days it should return.

Sheet 2 - currently in E4 I have a formula that looks up multiple criteria on a sumifs basis, at the moment E4 is returning a value of 20 as that is total sick days its picking up.
You can see on sheet one that row 6 end date falls into May therefor 5 days are in may and not April

On sheet 2 In E12 & F12 etc.. I have put how I would like it to return the values.



Any help would be great

 

[123rickfear]

cant see any attachments?

 

[jamesa2487]

How best can I send you a attachment as this is the best way to show what I'm trying to do and I see you cant add attachments on here

 

[123rickfear]

upload it to dropbox or similar, then post the link

 

[jamesa2487]

Here is the link, Thanks

https://uploadify.net/eaf8736bbc4ef98f/TEST.xlsm.xlsx

<tbody>
</tbody>

 

[jamesa2487]

So I seem to have two formulas that sort of do what I want but I just need to combine them,

Formula 1
This formula returns a value based on all the correct criteria but does not split the overlapping dates into the correct months

=SUMIFS('Sheet 1'!$G$3:$G$58,'Sheet 1'!$B$3:$B$58,$C5,'Sheet 1'!$F$3:$F$58,$A$1,'Sheet 1'!$H$3:$H$58,">="&Q$2,'Sheet 1'!$H$3:$H$58,"<="&Q$3)

Formula 2
This formula returns the correct days per month but is only working on 1 row rather than combining all the rows that fall under the sumifs criteria

=MAX(0,MIN(EOMONTH(Q2,0),$I3)-MAX(Q2,$H3)+1)