| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating an hourly matrix (scheduling)
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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" <clinton.holder.21jv0m_1137116402.0491@excelforu m-nospam.com wrote in message news:clinton.holder.21jv0m_1137116402.0491@excelfo rum-nospam.com... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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: http://www.excelforum.com/member.php...o&userid=30427 View this thread: http://www.excelforum.com/showthread...hreadid=500934 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating an hourly matrix (scheduling)
 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... -- 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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: http://www.excelforum.com/member.php...o&userid=30427 View this thread: http://www.excelforum.com/showthread...hreadid=500934 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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: http://www.excelforum.com/member.php...o&userid=30427 View this thread: http://www.excelforum.com/showthread...hreadid=500934 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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" <clinton.holder.21ng6m_1137283804.7408@excelforu m-nospam.com wrote in message news:clinton.holder.21ng6m_1137283804.7408@excelfo rum-nospam.com... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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: http://www.excelforum.com/member.php...o&userid=30427 View this thread: http://www.excelforum.com/showthread...hreadid=500934 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an hourly matrix (scheduling)
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: http://www.excelforum.com/member.php...o&userid=30427 View this thread: http://www.excelforum.com/showthread...hreadid=500934 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix calculation | Excel Discussion (Misc queries) | |||
| How to identify entries in a matrix also present in another list | Excel Worksheet Functions | |||
| Limits of MINV matrix function | Excel Worksheet Functions | |||
| Creating a matrix from columns | Excel Discussion (Misc queries) | |||
| Finding Values in a "Matrix" | Excel Discussion (Misc queries) | |||
Linear Mode
