creating an hourly matrix (scheduling)

[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. I dont know anything about arrays or macros but possibly I might need one of these to do the trick. ANY HELP APPRECIATED!!!

[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]...
>
> 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.
>
>
> --
> clinton.holder
> ------------------------------------------------------------------------
> clinton.holder's Profile:
> http://www.excelforum.com/member.php...o&userid=30427
> View this thread: http://www.excelforum.com/showthread...hreadid=500934
>

[clinton.holder]

OK, I made a few changes. I made it so the matrix is actually refrencing a different worksheet in the same workbook, but for some reason it wont calculate people that are working past midnight.

[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...

[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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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...
>
>
> --
> clinton.holder
> ------------------------------------------------------------------------
> clinton.holder's Profile:
> http://www.excelforum.com/member.php...o&userid=30427
> View this thread:
> http://www.excelforum.com/showthread...hreadid=500934
>

[clinton.holder]

This is a schedule that is used from week to week for some time. Is there a way of doing this without added the dates? Sorry the data above does not show any times that are past midnight. Just say for instance one of the start times was 4:00 PM and they worked until 2:00 AM the next morning. Im sure there is a function that could say if the outtime is less then the intime then add 24 hrs then do the subtraction or something... I just dont know how to change the funtion to say that.

[clinton.holder]

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