creating an hourly matrix (scheduling)

C

clinton.holder

Could anyone walk me through the functions needed for creating a matrix
that would look at a schedule worksheet and tell me the number of
people that are working at a specified time? Say I have a full
schedule, I want it to do similar to a tally for every hour like how
many people working at 04:30, then how many people working at 05:00 and
so on? I think it would be something to do with the COUNTIF function but
I'm not sure syntax.
 
J

JMay

Here's a small sample, give it a shot..

=SUMPRODUCT((--$C$14:$C$19<$F1)*(--$D$14:$D$19>$F1))


The above formula is in Cell G1 and copy down to G13.
Foramt cells as General.

Below are the content of cells F1, F2, F3.... F13

9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM


B C D
13 Time In Time Out
14 Paul 8:00 AM 3:50 PM
15 Mary 9:30 AM 2:30 PM
16 Peter 8:15 AM 4:30 PM
17 Tom 9:15 AM 3:15 PM
18 Alice 11:15 AM 2:15 PM
19 Jane 1:45 PM 5:00 PM


HTH

"clinton.holder" <[email protected]>
wrote in message
news:[email protected]...
 
C

clinton.holder

That doesnt seem to work. With the times you gave me I only entered the
first 2 and saw the error. When I put in the firs employees time It did
not show I would have them from 9:00am-03:50, and when I put in the
second employee which started at 9:30am it did not count them on the
clock until 10am. Any other suggestions?
 
C

clinton.holder

:rolleyes:
Ok, there was one small bug, I have to change the formula from just >
to >= so it would calculate the employee for the hour they start not
the next incriment. However can you help me with refrencing the times
from a different worksheet in the same workbook. I want to put the
matrix on the second worksheet...
 
C

clinton.holder

Thank you, that worked out excellent. I just made a change to support
tally of a person sarting at say 9:00am to show they are available
starting at that hour not the next interval. You helped me alot thought
THANKS AGAIN!
 
C

clinton.holder

OK, I made a few changes. I made it so the matrix is actually refrencin
a different worksheet in the same workbook, but for some reason it won
calculate people in that are working past 22:00. Any ideas
 
C

clinton.holder

Can anyone help with converting the formula above to accomidate the
calculation for people working past midnight? Currently it just doesnt
calculate people at all if their shift extends past midnight...
 
R

Roger Govier

Hi Clinton

With the data as posted, there is no way of knowing which day you are
dealing with.
Assuming your data related to 12 Jan 2006, did Jane start at 1:45 am on
the 12th, or the 13th?

I would insert a column at C with the start date, and a column at E with
the end date.
The formula in I1 copied down to I13
=SUMPRODUCT(--($C$14:$C$19<=$G1),
--($D$14:$D$19<$H1),--($E$14:$E$19>=$G1),
--($F$14:$F$19<=$H1))

will then return answers of 4,5,5,5,6,6,6,6,6,6,6,6,6 if she started
work on the 12th and 1 fewer in each case if she started work on the
13th.

--
Regards

Roger Govier


"clinton.holder"
message
news:[email protected]...
 
C

clinton.holder

This is a schedule that is used from week to week for some time. I
there a way of doing this without added the dates
 
C

clinton.holder

does anyone know how to make the adjustment to this formula, it shoul
be similar to counting someones hours past midnight right? something t
do with if the out-time is less than the intime then add 24hours an
then do the subtraction or something right
 
Top