Help making a schedule in Excel...

[Trisa]

I am designing a schedule for about 20 employees which includes a first and
second shift. The hours are not identical. For example, some work 6am-4pm,
9am-7pm, 9am-5pm, 2pm-12midnight, 4pm-12midnight, etc.

I am wondering if it is possible to have an automatic calculation for each
day how many staff are scheduled for first shift, and how many are scheduled
for second shift. Of course, I would have to define each shift and whether
it classifies as first or second. How do I do this?

Thank you so much for your help!

 

[Mr C]

I have been reading some of these replies and hopefully in this case I don’t
get too carried away for the answers to what you want.
There maybe something that you may want up front other than what I have here.

Worksheet 1
Headers – EIN, LastName, FirstName,
EIN A15
LastName B15
FirstName C15
Sunday thru Saturday E15 thru K15
Shift M15
Sunday thru Saturday N15 thru T15
Work Shift E14 center thru K14
Nu of Employees N14 center thru T14

Create a listing of your shifts such as 9am-7pm, 9am-5pm etc. as you had,
all in column V starting in row 14. That gives you your list of drop downs to
easy the input of the shifts.

I’m putting all this on one worksheet so you can see what happens. You may
want to break it up and put the results of how many employees are working
when on worksheet2.

Enter your employee’s information in columns A, B and C

You said you had 20 employees that is not a large number for this, but
follow along starting in E16, highlight down say to row 40, click on Data,
then Validation, click on the down arrow in Allow and choose Lists, down in
Source click on the little sq box and go and highlight your list of shifts
over in column V. Then click OK. Suggest before you go further test your
first entry place your cursor in E16 and there should be a drop down arrow
and your shifts should show click on the shift and press tab and the shifts
will be there. If OK highlight E16 thru E40 use the brush to copy the
functions to the remainder of the columns and rows in the Work Shift group,
E16 thru K40.

Now how many people are working each shifty on each day?

OK here in column M, row 16 enter you shifts in order such as 61m-4pm,
9am-5pm and etc. In N16 use the function =COUNTIF(E16:E40,"6am-4pm") In N17
change it to read =COUNTIF(E16:E45,"9am-5pm") and so on down until you reach
the end of the shifts. Copy N16 thru what ever (N20 based on the shifts you
gave) across O16 T20.

Now how many people you have working each day total – in N22 add =SUM(N16:N20)

Of course there is more you may want total hours work, costs each week what
week you want. But you should get what you want.

 

[Mr C]

Opps minor mistake sorry return E16 without the shift entry otherwise it will
copy it across the range. Highlight E16 down thru E40 and copy it to the
other 6 days. The other mistake is copy the sum function down in N22 across
the row for the other 6 days.