Count Start and End Dates that Overlap Specified Date Range

[DigDoug]

I am looking for some assistance in counting the number of events (with a start and an end date) that overlap with a specified date range.

So let's say that I have this table:

person	Start Date	End Date
A	6/30/12	1/15/13
B	2/15/13	4/28/13
C	10/05/13	2/14/14
D	1/1/12	12/31/12
E	6/30/12	6/30/14

I am looking for a formula that counts all Persons whose assignment duration overlaps in any way with the calendar year 2013. So, of the 5 persons on the list, 4 have an assignment duration that touches 2013.

This question has taken up most of my work day. I cannot seem to make countif work because I think I can only specify if an assignment start date occurs after 1/1/13 AND the end date is before 12/31/13.

Any help would be much appreciated!

[Ron Coderre]

with your sample data in A1:C6
this regular formula returns the count of persons where the date range includes at least one day from 2013

Please Login or Register  to view this content.

or...for more flexibility
E1: a year...2013
This regular formula adds that year as parameter to the above formula

Please Login or Register  to view this content.

With your sample data, both formulas return: 4

Is that something you can work with?

[DigDoug]

Thank you for this, but let me add a little wrinkle so I can be sure that I am on the right track here.

Let's look at this table instead, as it more closely resembles what I am trying to do:

Person	Type	Status	Start Date	End Date
1	A	ACTIVE	5/30/13	6/30/14
2	A	COMPLETE	10/1/12	4/15/13
3	B	COMPLETE	1/11/13	7/13/13
4	A	WITHDRAWN	8/15/13	7/15/14
5	A	COMPLETE	1/1/12	6/1/14
5	A	COMPLETE	1/1/12	6/1/12

Then, I need to count the rows that fulfill the following conditions:
- "A" Type
- Any status EXCEPT "Withdrawn"
- Assignment duration touched 2013

Again, thank you so much for your help!

[Ron Coderre]

Ok...Lets work with that.
With the new sample data in A1:E7
and
G1: Type
G2: A

H1: Status
H2: Withdrawn

I1: Year
I2: 2013

J1: Count

This regular formula returns the count of names that match that criteria

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS ALTERNATIVE:
Change the status, H2, to this: <>Withdrawn

Please Login or Register  to view this content.

For that data, both formulas return: 3

Does that help?

[DigDoug]

Christ. I think it worked! Next time I am up in Boston I'll have to buy you a beer.

Here's the formula that is now in the current workbook:
=SUMPRODUCT((ExpatDetails!$S:$S<>"withdrawn")*(ExpatDetails!$N:$N=Sheet2!$G$2)*(ExpatDetails!$H:$H=Sheet2!$A5)*(ExpatDetails!$P:$P<=--Sheet2!G$36)*(ExpatDetails!$Q:$Q>=--Sheet2!G$35))

whereas:
ExpatDetails!$S:$S is Status
ExpatDetails!$N:$N is Type
ExpatDetails!$H:$H is the Name of the group I am looking for
ExpatDetails!$P:$P is Start Date
ExpatDetails!$Q:$Q is end date
G$35 is specified start of date range
G$36 end of date range

The issue here now appears that since my dataset is egregiously large, it appears that my computer (which is no slouch) freezes up when I have it calculate. Is there any way to avoid this?

That question may not be appropriate for this forum, but at any rate - Thanks again!

[Ron Coderre]

Referencing entire columns is a BAD idea.
Try this:
Convert your data to Excel Tables
• Home.Format_as_Table
• Rename the table to something descriptive....tblProjectData
• Rebuild your formula, selecting the table data.
...Excel will create nice, descriptive, structured table references for you...something like this:

Please Login or Register  to view this content.

The upsides are:
- The formula is much more descriptive
- the table will automatically expand and contract to accommodate the data (like a dynamic named range), so Excel won't need to calculate on thousands of unused rows.

I hope that helps.

[DigDoug]

Thanks!

It does help, now I'm just not looking forward to rebuilding all of these formulas!

You've led me to another question regarding tables. The reason why I am trying to build all of these formulas is because I am looking to automate the analysis that is supposed to be done on this report on a weekly basis. When I receive a new report, can I just copy/paste the content of the report under the table headings, and have the formulas automatically recalculate based off of the new information that I am pasting over the old stuff?

-Doug

[Ron Coderre]

Yes! (most likely)
Be sure to Paste_Special.Values and the table will automatically expand to accommodate the new data AND the structured table references in your formula, since they reference the table fields, will return the correct values.

Note: if there are fewer rows in the new data then in the old data...delete all but the first table row before you paste the values.

[DigDoug]

Referencing entire columns is a BAD idea.
Try this:
Convert your data to Excel Tables
• Home.Format_as_Table
• Rename the table to something descriptive....tblProjectData
• Rebuild your formula, selecting the table data.
...Excel will create nice, descriptive, structured table references for you...something like this:

Please Login or Register  to view this content.

The upsides are:
- The formula is much more descriptive
- the table will automatically expand and contract to accommodate the data (like a dynamic named range), so Excel won't need to calculate on thousands of unused rows.

I hope that helps.

Another quick question on this. I formatted everything as a table and just renamed the table as "Data". Now when I try to reference Data[Status] in a formula I get a #VALUE! error. What could be causing this?