Date Range

K

KevMo

I'm setting up a spreadsheet that includes arrival and departure dates for
about 500 people. I'm trying to set it up so that I can figure out who will
be present on a given day. For example All people who will be present on
March 5. That range would include people arriving January 15 and departing on
March 6, but also people arriving March 5 and departing on April 10.

Any suggestions on setting up a function and sort command combination that
would allow me to do this.

Thanks
 
R

Roger Govier

Hi

With Arrivals in column A, Departures in column B.
In cell C2 enter your required Date e.g. 06/03/2006
In cell D2
=SUMPRODUCT(--($A$2:$A$100<=$C$2),--($B$2:$B$100>=$C$2))

Note that this includes the = 06/03/2006 in both parts, as people
leaving or arriving on that day will be present for some part of it.
Adjust the formula as required if you don't want this.
 
D

Duke Carey

Use the Data->Filter feature

Then it's a 2-step process. Filter on the Arrival column for arrival <=
3/5/06 and on the Departure column for >=3/5/06

Only the rows matching both filters will be displayed.

This sort of data retrieval is handled a little more smoothly in a database
(MS Access or MSDE) than in a spreadsheet
 
C

CLR

I would ust the Data > Filter > AutoFilter feature and use the Custom opeions
on the Arrival and departure columns for GreaterThan and LesserThan dates

Vaya con Dios,
Chuck, CABGx3
 
Top