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]

 

[Andrew Poulsom]

Peter,

I think your formula in F2 will return an incorrect result if you change say B7 to 20/6/14.

 

[Peter_SSs]

Peter,

I think your formula in F2 will return an incorrect result if you change say B7 to 20/6/14.

Andrew

Given the column B heading, I don't see how B7 could be that value.

If it could, then I would expect that A8 would become, 21/6/14 after which both our F2 formulas would again return the same value of 133.

 

[Andrew Poulsom]

Peter

I wouldn't have proposed such a complex formula had I thought that start date was always end date plus 1. The specific question was "How many days of a given date range in C2:D2, fall within multiple date ranges in columns A2:B13?". For me this implies that the multiple date ranges need not be continuous.

 

[Peter_SSs]

Peter

I wouldn't have proposed such a complex formula had I thought that start date was always end date plus 1. The specific question was "How many days of a given date range in C2:D2, fall within multiple date ranges in columns A2:B13?". For me this implies that the multiple date ranges need not be continuous.

Fair enough.

I was influenced by the combination of column headings, the actual sample dates given in columns A:B and the "12 date ranges" in the topic title. Posts #6 and #8 also referred to "months". Hence my suggestion.

Up to the OP to choose based on the actual circumstances.

 

[strooman]

Peter,
I think your formula in F2 will return an incorrect result if you change say B7 to 20/6/14.

Peter
I wouldn't have proposed such a complex formula had I thought that start date was always end date plus 1. The specific question was "How many days of a given date range in C2:D2, fall within multiple date ranges in columns A2:B13?". For me this implies that the multiple date ranges need not be continuous.

Fair enough.
I was influenced by the combination of column headings, the actual sample dates given in columns A:B and the "12 date ranges" in the topic title. Posts #6 and #8 also referred to "months". Hence my suggestion.

Up to the OP to choose based on the actual circumstances.

Well, as often in this kind of situations there is no absolute solution. Depends on the situation were to use the best solution. I discovered that Peter's example works for dates and also for hours I think both solutions are thorough and fit my needs. Thanks gentlemen.

At the moment I try to figure out what the:
ROW(INDIRECT(C$2+1&":"&D$2-1))
from Andrew does.

Works flawlessly but when I highlight that part and hit F9 to evaluate and give the actual values it gives me an #REF error. But the whole function works.

 

[Andrew Poulsom]

In Excel dates are just integers, as are rows. So that part of the formula returns an array of integers starting with the day after the first date and ending with the day before the last date. Each of those integers (dates) is passed in turn to the COUNTIFS function (returning 1 or 0) and the result is summed.

You can use the Evaluate Formula tool to see how it works.

 

[strooman]

I think it's best to stay in the same thread because the new question is the same in general.

I was a little to quick on the draw when I praised the versatality of Peter's formula for even working with hours. It works when I use a large interval 05:00-23:00 in C2:D2 which spans all given intervals in A2:B5. But fails when I use 05:00-09:00 in C2:D2.

I tried to modify Andrew's formula but it fails on the:

ROW(INDIRECT(C$2+1&":"&D$2-1))

part, because when you calculate with hours, 00:00 is just the number zero and 24:00 is 1. So because there is no row zero it fails. I'm just curious how to handle the same question but then with hours. So the question is:

How many hours of a given interval in C2:D2, fall within the intervals in columns A2:B5?
I'm interested in formulas in column E2:E5 (count per interval) and F2 (total count). Again, 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_Time[/TD]
[TD]End_Time[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Count[/TD]
[TD]Total_Count[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]05:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]06:00[/TD]
[TD="align: right"]07:00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]24:00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

I don't understand your results, but does this work for you?

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	7
3	06:00	07:00			2
4	20:00	22:00			3
5	22:00	24:00:00			1
Sheet1

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

 

[strooman]

I don't understand your results, but does this work for you?

Unfortunately not.

05:00 - 23:00 has to be split up (divided) over the intervals. That said:

05:00 - 06:00 = 1 hour, that goes into interval 00:00 - 06:00
06:00 - 07:00 = 1 hour that goes into interval 06:00 - 07:00
07:00 - 20:00 = 0 because there is no interval for that period (doesn't have to show up in the results)
20:00 - 22:00 = 2 hours that goes into interval 20:00 - 22:00
22:00 - 23:00 = 1 hours that goes into interval 22:00 - 00:00 (24:00)

 

[Andrew Poulsom]

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.