count scheduled staff between hours

[jat]

i am using the weekly schedule from the microsoft templates at
http://office.microsoft.com/en-us/templates/TC300023281033.aspx?pid=CT101172751033&WT.mc_id=42

i was wondering if it is possible to count the number of staff that are
scheduled for each hour.

ex. fill in the schedule for Sunday (assormtment of shifts); 10:00-16:00;
10:00-17:00; 13:00-17:00;

How many staff are scehduled between 10:00 - 11:00 on Sunday?
formula would return 2
How many staff are scheudled between 11:00 - 12:00 on Sunday?
formula would return 2
How many staff are scheduled between 12:00 - 13:00 on Sunday?
formulat would return 2
etc....

need to know if it can be done? i would be doing this for each day of the
week, for each hour between 7AM and 9PM. one value in each each.

any help to get me going would be appreciated.

thank you,

jat

 

[smartin]

i am using the weekly schedule from the microsoft templates at
http://office.microsoft.com/en-us/templates/TC300023281033.aspx?pid=CT101172751033&WT.mc_id=42

i was wondering if it is possible to count the number of staff that are
scheduled for each hour.

ex. fill in the schedule for Sunday (assormtment of shifts); 10:00-16:00;
10:00-17:00; 13:00-17:00;

How many staff are scehduled between 10:00 - 11:00 on Sunday?
formula would return 2
How many staff are scheudled between 11:00 - 12:00 on Sunday?
formula would return 2
How many staff are scheduled between 12:00 - 13:00 on Sunday?
formulat would return 2
etc....

need to know if it can be done? i would be doing this for each day of the
week, for each hour between 7AM and 9PM. one value in each each.

any help to get me going would be appreciated.

thank you,

jat

Here is a simple example in columns A-C


Staff In Out
A 10:00 AM 4:00 PM
B 10:00 AM 5:00 PM
C 1:00 PM 5:00 PM

Hour beginning # Staff
10:00 AM 2 (cell B7)
11:00 AM 2
12:00 PM 2
1:00 PM 3
2:00 PM 3
3:00 PM 3
4:00 PM 2
5:00 PM 0 (cell B14)

The only formulas are in B7:B14. The formula in B7 is

=SUMPRODUCT((A7>=$B$2:$B$4)*(A7<$C$2:$C$4))

Fill this down as needed.

Hope this gives you some ideas.

 

[amin]

How do I get this formula to count from PM to AM?