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?

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 havent 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 €œFs 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" wrote:

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?