# Creating a formula that analyzes whether or not individual was served between two dates

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.

• Show some test data so we can test our formulas. 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

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.

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