Calculating Staffing on a Schedule

[JC]

Hi Everyone,
I need a little help with a project I am working on.

I am in the process of making a schedule for work through Excel for all our
employees. I work at a hospital and we have all different shifts. We do the
schedule for 2 weeks at a time. What I did was make all the shifts people
work into a dropdown selectable list. Now the part I need help with is, I'd
like the spreadsheet to be able to count the number of personnel assigned for
each shift.

For example is I have 3 people selected as working 7a-3:30p, 2 people
7a-11:30p and 5 people 3p to 11:30pm, I want to be able at the bottom of the
spreadsheet, to automatically say that there are 5 people on day shift
(7a-3:30p) and then 7 people covering evening shift (3p-11:30). If someone
can tell me how I have to go about this or is able to help it me out, it
would be greatly apprecaited. If needed, I can send you my spreadsheet.

Thanks for all your help!

 

[Otto Moehrbach]

JC
If I understand you correctly, you have one column with the drop-downs.
Say this column is Column C and the shifts start with C2 and go to C50. So
in this column you will have a number of this shift, a number of that shift
and a number of the other shift. You want to know how many of each shift
you have. If this is correct, you need to use the COUNTIF function. Say
that , in the drop-down list, the first shift is written as "7a-3:30p".
Pick a cell in which you want the number of the first shift. In that cell
type =COUNTIF(C2:C50,"7a-3:30p"). That formula says, in English, look in
the range C2 to C50 and count the number of cells that have 7a-3:30p in
them. Do the same in 2 other cells for the other 2 shifts. Is this what
you want? Your numbers in your example are somewhat scrambled, so maybe you
want to add 2 shifts together. If so, write the formula like:
=COUNTIF(C2:C50,"7a-3:30p") + COUNTIF(C2:C50,"3p-11:30p"). HTH Otto

 

[JC]

Thank you for the quick reply.

Sorry for the confusion. Let me see if I can explain this a little better.
But I believe what you replied will work, if you can just let mek now if what
you explained is what I have described below: "Day Shift" is considered
anything between 7a-3:30p. While we have some people that work the entire day
shift, their shift is 7a-3:30p, we have some people that only work half of
the day shift and stay into second shift. Their shift might be 11a-11p
(meaning they are working 4 hours on days and 8 hours on evenings).

As you were saying for say today, Tuesday November 25: The entire schedule
for today would be listed from C5:C50, tomorrow would be listed from D5:50,
etc.

My dropdown list choices are made up of all the possible shifts, which I
listed below:
7a-11:30a
7a-3:30p
7a-7:30p
7a-11:30p
11a-3:30p
11a-7:30p
11a-11:30p
11a-3:30a
3p-7:30p
3p-11:30p
3p-3:30a
3p-7:30a
7p-11:30p
7p-3:30a
7p-7:30a
11p-3:30a
11p-7:30a
3a-7:30a
3a-11:30a
3a-3:30p
3a-7:30p

Thanks again for all your help.

 

[Otto Moehrbach]

JC
I don't know what you were getting at in your most recent post. Did you
mean to say that you need to break up a shift so that part of it is counted
in another shift? Post back and clarify what you mean. Otto

 

[JC]

Otto,
Hope this is clear. Some time frames need to be counted as coverage for
another shift.

For example, if I am counting the number working 7a to 3p and also 3p to 11p
and I have someone working 7a to 11p, that person would need to be counted
for both the day shift and also evening shift.

Hope that is clear

 

[Otto Moehrbach]

JC

Excel is a beast of logic so I'm trying to put what you say into a logic
that Excel can understand. I assume that 7a-3:30p is the first or day
shift, 3p-11:30p is the evening or second shift, and 11:30p-7:30a is the
third or night shift. Is that right? Assuming it is, I've made the
following table where "A" is the first shift, "B" is the second shift, and
"C" is the third shift. A combination of 2 or 3 of those 3 letters means
that someone working that shift should be counted in each of those letter
shifts. Does that make sense? If it does, are the combinations I show
correct? Otto

7a-11:30a A

7a-3:30p A

7a-7:30p AB

7a-11:30p ABC

11a-3:30p A

11a-7:30p AB

11a-11:30p AB

11a-3:30a A

3p-7:30p B

3p-11:30p B

3p-3:30a ABC

3p-7:30a BC

7p-11:30p B

7p-3:30a BC

7p-7:30a BC

11p-3:30a C

11p-7:30a C

3a-7:30a C

3a-11:30a CA

3a-3:30p CA

3a-7:30p CAB

 

[JC]

Yes, I've corrected below. Basically you were correct, I just made a few
changes.

If you have an better idea on how I can go about listing these, I am welcome
to suggestions also.

 

[Otto Moehrbach]

JC

You said that each day's schedule is in rows 5:50 in sequential
columns. When you run this macro (below) Excel will have to find the column
with which you want Excel to work. There are several ways for Excel to find
this column. I chose to have Excel find the last occupied cell in row 5.
That will then be the column. If this doesn't suit you for any reason, like
maybe you have entries in row 5 to the right of the pertinent column, let me
know and I'll change how it finds the pertinent column. Note that with this
method of finding that column, the wrong column will be selected if the cell
in row 5 of that column is empty. Another way to find that column would be
for you to select a cell, any cell, in that column BEFORE you run the macro,
but, for now, the first method is used.

I assumed that the list of employees (names) are in Column A
starting with A5 and down to the last entry in Column A. This way, the
number of employees is not limited. The sum of each shift is placed
immediately below the last entry in the column that has the shifts.

Look at the macro. Find the line that says "Select Case." and
the line that says "End Select". All the shifts you provided are listed
between these 2 lines. It is very important that the text of the shifts
that you have in your drop-down cells match EXACTLY the text shown in the
macro. Any extra spaces here and there will cause a mismatch and,
subsequently, an error in the results.

If you wish, send me an email and I'll send you the small file I
used to develop this macro. That file has this macro properly placed and a
button on the sheet to execute this macro. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto

Sub SumShifts()
Dim TheCol As Long, rColA As Range, Dest As Range
Dim TheShift As String, ShiftCode As String
Dim i As Range, CountA As Long, CountB As Long
Dim CountC As Long
TheCol = Cells(5, Columns.Count).End(xlToLeft).Column
Set rColA = Range("A5", Range("A" & Rows.Count).End(xlUp))
Set Dest = rColA(rColA.Count).Offset(1, TheCol - 1)
CountA = 0
CountB = 0
CountC = 0
For Each i In rColA
If IsEmpty(i.Offset(, TheCol - 1)) Then _
GoTo NextEmployee
TheShift = i.Offset(, TheCol - 1).Value
Select Case TheShift
Case "7a-11:30a", "7a-3:30p", "11a-3:30p": ShiftCode = "A"
Case "7a-11:30p", "11a-7:30p", "11a-11:30p": ShiftCode = "AB"
Case "11a-3:30a": ShiftCode = "ABC"
Case "3p-7:30p", "3p-11:30p", "7p-11:30p": ShiftCode = "B"
Case "3p-3:30a", "3p-7:30a", "7p-3:30a", "7p-7:30a": ShiftCode =
"BC"
Case "11p-3:30a", "11p-7:30a", "3a-7:30a": ShiftCode = "C"
Case "3a-11:30a", "3a-3:30p": ShiftCode = "CA"
Case "3a-7:30p": ShiftCode = "CAB"
End Select
If InStr(ShiftCode, "A") > 0 Then CountA = CountA + 1
If InStr(ShiftCode, "B") > 0 Then CountB = CountB + 1
If InStr(ShiftCode, "C") > 0 Then CountC = CountC + 1
NextEmployee:
Next i
Dest.Value = "Shift A= " & CountA
Dest.Offset(1).Value = "Shift B= " & CountB
Dest.Offset(2).Value = "Shift C= " & CountC
End Sub