Hi,

I have the following data -

Row Date Time in Time out
1 1/1/08 1:05 AM 1:40 AM
2 1/1/08 1:15 AM 2:04 AM
3 1/1/08 1:30 AM 1:59 AM
4 1/1/08 1:45 AM 1:50 AM
5 1/1/08 2:01 AM 2:15 AM

I am trying to find out the max census by hr. Below is the report I am
looking to create for each day of the year.
------------------------------------------------------------------------------------------------
Date Between 1-2 AM Between 2-3 AM ............
1/1/08 3
1/2/08
------------------------------------------------------------------------------------------------
I figured out the logic but can't figured out. The logic is the (Count of
Time In between 1 and 2 AM) - (Count of corresponding Time out that are less
than any of the Time In)

So between 1-2 AM there are 4 patients in. But the Time out (1:40 AM) is
less than Time In (1:45 AM).

Therefor max census is 4-1 = 3.

I hope all this makes sense :)

Can you help me figure a way to do this?

Thanks!!

Let's assume the dates are in A2:A30, Time In in B2:B30, Time Out in C2:C30
In F2 enter the required date (1/1/09), in G2 enter the required start time
(1:00 AM), in H2 the required stop time (I use 1:59 AM but you could use
2:00 AM)
In I2 is used =SUMPRODUCT(--(A2:A30=F2),--(B2:B30=G2),--(C2:C30<=H2))
This returned the answer 3 as required
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"sreeks" wrote in message
...
Hi,

I have the following data -

Row Date Time in Time out
1 1/1/08 1:05 AM 1:40 AM
2 1/1/08 1:15 AM 2:04 AM
3 1/1/08 1:30 AM 1:59 AM
4 1/1/08 1:45 AM 1:50 AM
5 1/1/08 2:01 AM 2:15 AM

I am trying to find out the max census by hr. Below is the report I am
looking to create for each day of the year.
------------------------------------------------------------------------------------------------
Date Between 1-2 AM Between 2-3 AM ............
1/1/08 3
1/2/08
------------------------------------------------------------------------------------------------
I figured out the logic but can't figured out. The logic is the (Count of
Time In between 1 and 2 AM) - (Count of corresponding Time out that are
less
than any of the Time In)

So between 1-2 AM there are 4 patients in. But the Time out (1:40 AM) is
less than Time In (1:45 AM).

Therefor max census is 4-1 = 3.

I hope all this makes sense :)

Can you help me figure a way to do this?

Thanks!!