1

I am writing a grant report in which I need to count the number of individuals who received a service during a specific quarter. That is, if the person was in our shelter any day between 10/1/2015 and 12/31/2015.

The way the data is displayed, their entry date and their exit date is recorded. What I'm struggling with is, if I write a countifs formula looking at whether or not their entry date OR their exit date occurs between 10/1/2015 and 12/31/2015, I may miss individuals who entered on 9/30/2015 or earlier and exited on 1/1/2016 or later.

Those individuals may have been in shelter every day from 10/1/2015 to 12/31/2015, but they would not be counted because their entry and exit dates occur outside of the date range 10/1/2015 to 12/31/2015.

It may be that I need to do this in multiple steps (multiple columns doing different calculations that I can use to get the end result), but I'm not sure where to begin.

Improve this question
2
  • 1
    Show some test data so we can test our formulas.
    – Scott Craner
    Apr 27, 2016 at 18:27
  • This shows stay data for seven participants: Shelter Shelter Shelter Shelter Information Information Information Information Stay 1 – Stay 1 - Stay 2 – Stay 2 – Entry Date Exit Date Entry Date Exit Date 10/24/2015 10/26/2015 10/24/2015 10/26/2015 9/25/2015 1/7/2016 1/8/2016 4/18/2015 4/20/2015 6/7/2015 7/17/2015 12/4/2015 12/12/2015 9/11/2015 9/14/2015 9/19/2015 9/21/2015
    – JEB
    May 2, 2016 at 21:37

1 Answer 1

Reset to default
4

You are checking if two intervals overlap.

I will assign your Quarter from Q1-Q2, and served from S1-S2

Q1 being the first date of the Quarter, Q2 being the end date of the Quarter.

S1 being the first date of service, S2 being the last date of service

Basically you need to check

=if(and(S2>=Q1,S1<=Q2),"They stayed here","They didn't stay here")

This should cover all four cases in the image below.

enter image description here

Improve this answer
2
  • Added comments about S1,Q1... If they want actual dates they can provide data and I would have put it in an actual formula.
    – gtwebb
    Apr 27, 2016 at 19:02
  • 2
    nice visual aid.
    – Scott Craner
    Apr 27, 2016 at 19:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .