Split Dates formula

jamesa2487

New Member
Joined
Sep 22, 2017
Messages
4
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
smile.gif
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top