software

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: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934Here's a small sample, give it a shot..

=SUMPRODUCT((--$C$14:$C$19lt;$F1)*(--$D$14:$D$19gt;$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 PMB 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 PMHTH

quot;clinton.holderquot; lt;clinton.holder.21jv0m_1137116402.0491@excelforu m-nospam.comgt;
wrote in message
news:clinton.holder.21jv0m_1137116402.0491@excelfo rum-nospam.com...
gt;
gt; Could anyone walk me through the functions needed for creating a matrix
gt; that would look at a schedule worksheet and tell me the number of
gt; people that are working at a specified time? Say I have a full
gt; schedule, I want it to do similar to a tally for every hour like how
gt; many people working at 04:30, then how many people working at 05:00 and
gt; so on? I think it would be something to do with the COUNTIF function but
gt; I'm not sure syntax.
gt;
gt;
gt; --
gt; clinton.holder
gt; ------------------------------------------------------------------------
gt; clinton.holder's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30427
gt; View this thread: www.excelforum.com/showthread...hreadid=500934
gt;

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?--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934

Ok, there was one small bug, I have to change the formula from just gt;
to gt;= 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...--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934
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!--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934
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 in that are working past 22:00. Any ideas?--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934
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: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934Hi 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$19lt;=$G1),
--($D$14:$D$19lt;$H1),--($E$14:$E$19gt;=$G1),
--($F$14:$F$19lt;=$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 Govierquot;clinton.holderquot;
lt;clinton.holder.21ng6m_1137283804.7408@excelforu m-nospam.comgt; wrote in
message
news:clinton.holder.21ng6m_1137283804.7408@excelfo rum-nospam.com...
gt;
gt; Can anyone help with converting the formula above to accomidate the
gt; calculation for people working past midnight? Currently it just doesnt
gt; calculate people at all if their shift extends past midnight...
gt;
gt;
gt; --
gt; clinton.holder
gt; ------------------------------------------------------------------------
gt; clinton.holder's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30427
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=500934
gt;

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?--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934
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?--
clinton.holder
------------------------------------------------------------------------
clinton.holder's Profile: www.excelforum.com/member.php...oamp;userid=30427
View this thread: www.excelforum.com/showthread...hreadid=500934