Identify minimum staffing based on shift hours

[Pjb]

I am looking for a way to identify if minimum staffing is met for each shift(bottom data) based on the shift hrs each person is working with a status of "ON" (top data).


I would really appreciate any help - I'm stumped on this one. Thanks!!!


Name Shift Status

Smith 600-1430 ON

Jones 600-1430 ON

Johnson 630-1430 OFF

William 1030-2230 SICK

Black 1430-2230 ON

Adams 1500-300 VAC

George 1800-600 ON

Cantu 2230-630 ON


HOURS MIN Staffing Count

0600-1030 7 ??

1030-1430 8 ??

1430-1600 9 ??

1600-1800 9 ??

1800-2230 9 ??

2230-0300 8 ??

0300-0600 7 ??

 

[fred]

you can use if + sumproduct. That said, I don't understand why


10:30 - 14:30 would produce all 8 person present, even when some is on vacation or sick or off.

 

[NARAYANK991]

Hi ,


Can you confirm whether the following is the result you would like to get ?


Hours Total Staff Actual Count

0600-1030 ..... 7 ..... 2

1030-1430 ..... 8 ..... 2

1430-1600 ..... 9 ..... 1

1600-1800 ..... 9 ..... 1

1800-2230 ..... 9 ..... 2

2230-0300 ..... 8 ..... 2

0300-0600 ..... 7 ..... 2


Narayan

 

[Jeanbar]

Pjb,


I posted an answer to your question in another post http://chandoo.org/forums/topic/find-if-2-sets-of-times-overlap-calculate-time-overlapped-even-through-midnight?replies=19#post-14236


The crux of the problem is to manage the shift period crossing 2 days.

 

[Jeanbar]

Narajan,


Are you sure of your last line ?

[pre]

Hours Total Staff Actual Count
...
0300-0600 ..... 7 ..... 2

[/pre]

Shoudn't it be zero ?

 

[NARAYANK991]

Hi Jean ,


In the original post , the last two staff are scheduled as follows :


George 1800-600 ON

Cantu 2230-630 ON


Both of them should be available during the 0300 - 0600 period. Is this right ?


Narayan

 

[Jeanbar]

Narajan,


I understood it the other way round: the status of "ON" means that the person is on-duty in this particular shift and off-duty outside the period. So in the shift 0300-0600 there is nobody available with the data provided. Perhaps Pjb could confirm ?

 

[Pjb]

The status of "ON" does mean to count that person to try and meet minimum staffing.


I gave only a few examples of actual people working a shift.


NARAKANSK991 - the results you have are the correct results I am looking for.


Thanks for any help you can provide!!!!

 

[NARAYANK991]

Hi ,


I have uploaded a worksheet whose link is given below. Please confirm if everything is OK ; try the same worksheet with a different set of data.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21110


Narayan