Count days date range within 12 date ranges

strooman

Active Member
Joined
Oct 29, 2013
Messages
333
Office Version
  1. 2016
Platform
  1. Windows
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]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Does this work for you?

=SUMPRODUCT(COUNTIFS(A2:A13,"<="&ROW(INDIRECT(C2+1&":"&D2-1)),B2:B13,">="&ROW(INDIRECT(C2+1&":"&D2-1))))
 
Upvote 0
Could you clarify the logic of the green 16 (in E4) and 26 (in E8)?
To my mind, the are 14 days in March NOT in the given period in C2:D2, meaning there would be 17 that are in the period.
Similarly there appear to me to be 27 July days IN the period.

If your figures are right then if C2 was 1 January 2014 and D2 was 31 January 2014 then E2 would be 29, is that correct?
 
Upvote 0
Could you clarify the logic of the green 16 (in E4) and 26 (in E8)?
To my mind, the are 14 days in March NOT in the given period in C2:D2, meaning there would be 17 that are in the period.
Similarly there appear to me to be 27 July days IN the period.

If your figures are right then if C2 was 1 January 2014 and D2 was 31 January 2014 then E2 would be 29, is that correct?

Yes sire, your assumption is correct. I don't want to include the Start and End in my calculations. The 30, 31 and 30 are typo's. Sorry for the confusion.
 
Last edited:
Upvote 0
Thank you Andrew. Works good for me. Outcome is 133. That was the count I was looking for.
What could I use as a formula in column E so I can see the count for each month (as I displayed in my example)?
 
Upvote 0
You can make a slight amendment to that formula:

=SUMPRODUCT(COUNTIFS(A2,"<="&ROW(INDIRECT(C$2+1&":"&D$2-1)),B2,">="&ROW(INDIRECT(C$2+1&":"&D$2-1))))
 
Upvote 0
You can make a slight amendment to that formula:

=SUMPRODUCT(COUNTIFS(A2,"<="&ROW(INDIRECT(C$2+1&":"&D$2-1)),B2,">="&ROW(INDIRECT(C$2+1&":"&D$2-1))))


Thanks very much mister Polsom, works perfect for me. Problem solved.

@Peter_SSs: Sorry I'm mixing and messing things up. I should be more precise. Giving the example 15-03-2014 - 27-07-2014. I don't want to count 15-03 and 27-07. But when the range spans the whole month, just count ALL days of that month. So the days in green 16-30-31-30-26 are the correct values. Please ignore post #4. The formules given by Andrew Polsom do that perfectly. Sorry for the confusion.
 
Upvote 0
If Andrew's formulas produce the correct results for you, then I would suggest some others to consider as well.

For the Total_Count
You may have changed from Andrew's formula to one that sums the column E values. If not, or an any case, doesn't the F2 formula below produce the same result?


For the individual month Count values
This E2 (& copied down) formula, that avoids the volatile function INDIRECT has produced the same results as Andrew's for all the samples I have tried*.

* There is one exception - start and end dates are one day apart. If the dates do not span the whole month then the first and last days are to be excluded. So if the start date was 5 April 2014 and the end date was 6 April 2014 this does not span the whole month of April so after excluding the end dates the count would be 0, which my formula returns, whereas Andrew's returns 2 for that example.

Excel Workbook
ABCDEFGH
1Start_Of_MonthEnd_Of_MonthStartEndCount (PS)Total_Count (PS)Count (AP)Total_Count (AP)
21/01/1431/01/1415/03/1427/07/1401330133
31/02/1428/02/14.00
41/03/1431/03/141616
51/04/1430/04/143030
61/05/1431/05/143131
71/06/1430/06/143030
81/07/1431/07/142626
91/08/1431/08/1400
101/09/1430/09/1400
111/10/1431/10/1400
121/11/1430/11/1400
131/12/1431/12/1400
Count Dates
 
Upvote 0
Thanks Peter for the conveniently arranged lay-out. I wish "Excel Jeanie" could work with Windows 8 64 bit, but that's another discussion.
Ha ha, very simpel solution in F2. Nice example where we sometimes tend to make things too difficult. Didn't know that indirect was a volatile function. I will certainly use Andrew's or your examples in my "shifts for employees" orientated spreadsheet.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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