Count days date range within 12 date ranges

[strooman]

How many days of a given date range in C2:D2, fall within multiple date ranges in columns A2:B13?
I'm interested in formulas in column E2:E13 (count per period) and F2 (total count). I typed in the desired results in Green.

Help is much appreciated.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Start_Of_Month[/TD]
[TD]End_Of_Month[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Count[/TD]
[TD]Total_Count[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1-1-2014[/TD]
[TD="align: right"]31-1-2014[/TD]
[TD="align: right"]15-3-2014[/TD]
[TD="align: right"]27-7-2014[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]133[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1-2-2014[/TD]
[TD="align: right"]28-2-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1-3-2014[/TD]
[TD="align: right"]31-3-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1-4-2014[/TD]
[TD="align: right"]30-4-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1-5-2014[/TD]
[TD="align: right"]31-5-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1-6-2014[/TD]
[TD="align: right"]30-6-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1-7-2014[/TD]
[TD="align: right"]31-7-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1-8-2014[/TD]
[TD="align: right"]31-8-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1-9-2014[/TD]
[TD="align: right"]30-9-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1-10-2014[/TD]
[TD="align: right"]31-10-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1-11-2014[/TD]
[TD="align: right"]30-11-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1-12-2014[/TD]
[TD="align: right"]31-12-2014[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[strooman]

I don't understand your numbers sorry. There are 3 hours between 20:00 and 22:00 (20:00, 21:00 and 22:00). Perhaps you meant to start at 22:01.

From 20:00 to 22:00 = from 20:00 to 21:00 AND from 21:00 to 22:00. That makes 2 to me.

 

[Andrew Poulsom]

In that case why were there 30 days between 1 June and 30 June in your original post?

 

[strooman]

In that case why were there 30 days between 1 June and 30 June in your original post?

I understand and agree with your logical and unambiguously approach of the given problem. Unfortunately my supervisor wants it to calculate it that way. I tried to convince him that calculations for dates should be consistent with the calculations of the hours but that turned out to be a pointless effort.

So the calculations of hours should be slightly different then the dates but I think it can be down with the same formulas (with adjustments). I'm sorry for the confusion. Let me give a very straight example:
When a person works from 18:00 till 23:00 and he/she gets paid a special rate (122%) from 20:00 - 22:00 and a special rate(144%) from 22:00 - 23:00 then he gets that rate respectively for 2 hours and 1 hour. Hope this clarifies things and your help is much appreciated.

 

[Andrew Poulsom]

OK try:

Excel 2010
	A	B	C	D	E	F
1	Start_Time	End_Time	Start	End	Count	Total_Count
2	00:00	06:00	05:00	23:00	1	5
3	06:00	07:00			1
4	20:00	22:00			2
5	22:00	24:00			1
Sheet1

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(COUNTIFS(A2,"<"&(ROW(INDIRECT((C$2*24)+1&":"&(D$2*24)))/24),B2,">="&(ROW(INDIRECT((C$2*24)+1&":"&(D$2*24)))/24)))
F2	=SUMPRODUCT(COUNTIFS(A2:A5,"<"&(ROW(INDIRECT((C$2*24)+1&":"&(D$2*24)))/24),B2:B5,">="&(ROW(INDIRECT((C$2*24)+1&":"&(D$2*24)))/24)))

 

[Peter_SSs]

I'm not sure I have a full understanding of the requirement, but I'll jump back in with another try.
Is this close? If not, some more examples and expected results might help clarify.

Formula in E2, copied down.

Excel Workbook
	A	B	C	D	E	F
1	Start_Time	End_Time	Start	End	Count	Total_Count
2	0:00	6:00	5:00	23:00	1	5
3	6:00	7:00			1
4	20:00	22:00			2
5	22:00	24:00:00			1
Count Times

 

[strooman]

Gentlemen, you both deserve a very big compliment. Not only for bringing in the right solution but even more for staying with me. I realize that my explanations were not always clear or unambiguously but during this process you kept looking for solutions. I really appreciate that. Men like you make this forum rock and very valuable.
This thread can go in the books as SOLVED. Thanks Peter and Andrew.

 

[Peter_SSs]

Glad you got what you wanted.

Assuming my formula for column E in post #25 is producing what you want, then an adaptation of that should (I think) provide a simpler solution for that column of the earlier 'Dates' problem, even if the dates in columns A:B are not sequential, as follows.

Excel Workbook
	A	B	C	D	E
1	Start_Of_Month	End_Of_Month	Start	End	Count
2	1/01/14	31/01/14	15/03/14	27/07/14	0
3	1/02/14	28/02/14		.	0
4	1/03/14	31/03/14			16
5	1/04/14	30/04/14			30
6	1/05/14	31/05/14			31
7	1/06/14	20/06/14			20
8	1/07/14	31/07/14			26
9	1/08/14	31/08/14			0
10	1/09/14	30/09/14			0
11	1/10/14	31/10/14			0
12	1/11/14	30/11/14			0
13	1/12/14	31/12/14			0
Count Dates