Determine # in system with Arrivals and Departures data

M

megv

I will try to be as clear as possible. I have tons of data with the time/date of the data in date format. It is data of customers arrivals and departures. What I would like to find is the number in the system at specified time periods. Especially at each of the customers arrivals.

Below is a small sampling of the data. I thought I could use counts and if statements. but I seem to go nowhere. Each row of data is the arrival time and departure time for a particular custome

ARRIVAL DEPARTUR
12/31/00 21:22 1/1/01 1:2
12/31/00 21:37 1/1/01 0:3
12/31/00 22:00 1/1/01 0:3
12/31/00 22:13 1/1/01 0:0
12/31/00 22:12 1/1/01 0:2
12/31/00 22:15 1/1/01 0:1
12/31/00 23:16 1/1/01 0:3
12/31/00 23:34 1/1/01 3:1
12/31/00 23:36 1/1/01 1:4
12/31/00 23:49 1/1/01 0:3
1/1/01 0:07 1/1/01 2:2
1/1/01 0:12 1/1/01 1:0
1/1/01 0:13 1/1/01 0:5
1/1/01 0:38 1/1/01 2:1
1/1/01 0:42 1/1/01 3:1
1/1/01 0:51 1/1/01 1:5
1/1/01 0:51 1/1/01 3:1
1/1/01 0:58 1/1/01 2:0
1/1/01 1:32 1/1/01 2:5

Any help will be greatly appreciate

Thank
Meggie
 
N

nilizandr

Hi,
I did this in the attached spreadsheet.
Use paste special->transpose to move the column data into a row. (se
row 1)
Use "if" statements, with zeros and ones to check every possibl
arrival/departure combination.
Then sum the data points for any given arrival time (see row 22) to se
how many people are in the system at that time.
Hope this is self-explanatory

Attachment filename: arrivals.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51195
 
D

Dave Peterson

One moment in time?

If yes, I put your data in A1:B20.

I put my single moment in time in A23 (including the date) and used this
formula:

=SUMPRODUCT(--(A2:A20<=A23),--(B2:B20>=A23))

I put this formula in C2 and dragged down:

=SUMPRODUCT(--($A$2:$A$20<=A2),--($B$2:$B$20>=A2))

And got a running total based on that arrival time.

Close?
 
M

Max

Maybe try this previous response ?:

http://tinyurl.com/2r7gl
(Subject: Ward occupancy chart)

which is quite relevant and works on
data with structure similar to what you posted

The "Occupancy" derivation in the thread
would be equivalent to your desired "# in system"

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

megv said:
I will try to be as clear as possible. I have tons of
Click to expand...
data with the time/date of the data in date format. It is
data of customers arrivals and departures. What I would
like to find is the number in the system at specified time
periods. Especially at each of the customers arrivals.
Below is a small sampling of the data. I thought I could
Click to expand...
use counts and if statements. but I seem to go nowhere.
Each row of data is the arrival time and departure time
for a particular customer
 
M

Max

If you're interested to have the sample book
(Tracking of Occupancy per hourly band)
just post a "readable" email address here

Sample is easily adaptable to suit
 
Top