count how many staff are scheduled between...

[jatman]

i am using a template from office.excel for a schedule.

i want to know if it is possible to do the following:

Column A: Employee Name
Column B: Sunday Start Time
Column C: Sunday End Time
Column D: Monday Start Time....

Row 5: Emp 1
Row 6: Emp 2
Row 7: Emp 3...

Emp 1, Sunday schduled 10:00 to 17:00
Emp 2, Sunday schduled 10:00 to 12:00
Emp 3, Sunday schduled 11:00 to 16:00...

in cell A15, i want to know how many people are schduled on Sunday between
10:00 and 11:00 (formula would return 2 - because two people are schduled to
be there between 10 and 11 AM); in A16, time frame would be 11AM to 12 PM
and the value returned would be 2;

can this be done in excel?

 

[B. R.Ramachandran]

Hi,

From what I understand from your example, the employee and their time data
start at Row 3, and that there are two columns (start time and end time) for
each day, i.e., Columns B and C for Sunday, Columns D and E for Monday, and
so on. Since you haven’t mentioned the number of employees (and therefore
the last row), let us assume that the last row is Row 12 (Modify the formulas
appropriately to reflect the correct ranges)

In Cells A15, A16, A17,……A29, enter 7:00, 8:00, 9:00,……21:00.

In B15, enter the following formula, and drag the formula down to B28.
=SUMPRODUCT(($B$3:$B$12<=$A15)*($C$3:$C$12>=$A16))

B15, B16, ….B28 will show the numbers of people scheduled to be there
during the hours 7:00-8:00, 8:00-9:00, …… 20:00-21:00 respectively.

Change the “Bâ€s and “Câ€s in the formula to “Dâ€s and “Eâ€s respectively for
Monday (enter the formula maybe in D15), to “F’s and “Gâ€s for Tuesday (maybe
in F15), and so on.

Hope this helps,
B. R. Ramachandran

P.S: By the way, shouldn't the number of people working in the time frame
11:00-12:00 be 3 (not 2 as you have indicated)?

 

[jatman]

Yes, you are correct, it should be three..., i was still on my secon
cup of coffee...

the formula worked, thank you. i just have to tweak it a bit i
possible for the text that would be entered, otherwise it works great.

thank you,

jat

 

[B. R.Ramachandran]

Hi,

Thanks for the feedback. If the formula was useful, please click the "Yes"
button.

Thanks,
B. R. Ramachandran

Yes, you are correct, it should be three..., i was still on my second
cup of coffee...

the formula worked, thank you. i just have to tweak it a bit if
possible for the text that would be entered, otherwise it works great.

thank you,

jat

Hi,

......
Hope this helps,
B. R. Ramachandran

P.S: By the way, shouldn't the number of people working in the time
frame
11:00-12:00 be 3 (not 2 as you have indicated)?
QUOTE]

--
jatman
------------------------------------------------------------------------
jatman's Profile: http://www.thecodecage.com/forumz/member.php?userid=1082
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=147095

.

 

[aminh]

Hi,

Thanks for the feedback.  If the formula was useful, please click the "Yes"
button.

Thanks,
B. R. Ramachandran

Yes, you are correct, it should be three..., i was still on my second
cup of coffee...

the formula worked, thank you.  i just have to tweak it a bit if
possible for the text that would be entered, otherwise it works great.

thank you,

jat

Hi,
......
Hope this helps,
B. R. Ramachandran
P.S:  By the way, shouldn't the number of people working in the time
frame
11:00-12:00 be 3 (not 2 as you have indicated)?
QUOTE]

.- Hide quoted text -

- Show quoted text -

How do you get this formula to count the shifs that start in the PM
and ends in the AM?