Excel 2007 - Time Schedule Question

[MiagiSan]

Hi Everyone,

I have a staff schedule presented to me in Excel. (I am using 2007)

A 1,2 Merged (Person1)
A 3,4 Merged (Person2)

B1 - 9:00AM (Start of shift for person 1)
B2 - 5:00PM (End of Shift for person 1)
B3 - 10:00AM (Start of Shift for person 2)
B4 - 6:00PM (End of Shift for person 2)

I am not sure if this is possible but I would like to be able to easily show how many people are available for each hour of the day.

Using the data from above, the below output should show like so.

People Available by Hour
12:00 AM - 0
1:00 AM - 0
2:00 AM - 0
3:00 AM - 0
4:00 AM - 0
5:00 AM - 0
6:00 AM - 0
7:00 AM - 0
8:00 AM - 0
9:00 AM - 1
10:00 AM - 2
11:00 AM - 2
12:00 PM - 2
1:00 PM - 2
2:00 PM - 2
3:00 PM - 2
4:00 PM - 2
5:00 PM - 1
6:00 PM - 0
7:00 PM - 0
8:00 PM - 0
9:00 PM - 0
10:00 PM - 0
11:00 PM - 0


I have tried an IF statement however it would not allow me to add more than one argument to the formula. Does anyone know if this is possible?

I appreciate your help!

 

[kjv1611]

Have you tried a Pivot table?

 

[kjv1611]

Here's a trainig link that comes from Excel's help file:

http://office.microsoft.com/trainin...RC102058721033&pid=CR100479681033&ofcresset=1

 

[SkipVought]

hi,

1. DO NOT use merged cells. You will be shooring yourself in the foot. Put the NAME on each row that relates to that person. Use Conditional Formatting to "hide" the font color if necessary.

2. change your FIRST table to this structure...
[tt]
Person In Out
a 9:00 13:00
b 10:00 14:00
[/tt]
Name your range in accordance with the table headings.

Use this formula...
[tt]
=SUMPRODUCT(--(In<=A2)*(Out>A2))
[/tt]
where A2 is your first date. copy down


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MiagiSan]

Thank you very much!!

 

[SkipVought]

This might be better...
[tt]
B2: =SUMPRODUCT(--(In<=A2)*(Out>=A2))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}