Excelling at Scheduling

Column: Takin’ Care of Business
By Scott L. Clark


In 2005, I walked into AMCORE Bank a month before my wedding. Funds were getting a little tight, and I was worried I wasn’t going to make my vehicle payment and wanted to see if I could make arrangments somehow. I spoke with the Branch Manager there, who told me that he couldn’t help me with my loan payment, but was curious if I would be interested in being a bank manager. Three interviews later, and I was the Assistant Branch Manager for the 6th Street branch downtown with no prior banking experience or management experience. Needless to say, I had a lot of learning to catch up on very quickly. The following tool I created a couple years later as a manager would have been very handy during that first year.

One of my first tasks assigned to me as a manager was to schedule everyone each week so that I had proper coverage at my branch while keeping everyone under their allowed hours. Overtime was not acceptable ever, so it was important to have something that easily calculated hours and lunch breaks as well as give my staff a quick and easy way to see when they needed to work. After several hours, I put together the following spreadsheet to help me manage my branch’s schedule. Not only was I able to use it myself, but it was eventually used by every branch in my region. I was even asked to present it in front of my peers to teach them how to use it in their branches.

The best way to learn how to use this spreadsheet is to just play around with it. Download the attachment (click on the link at the end of this post), and start to mess around with it. I’ve made the sheet “dummy proof” so unless you know how to unlock cells, you really can’t screw it up. And if you do, I can always e-mail you a blank copy.

The first thing to notice when you open up the spreadsheet is that there are 6 tabs across the bottom. The first five are each week of the month (if there are only 4 weeks in your month, just leave the last one alone). The last tab is where you will enter your employees names, which will be transferred to the appropriate week’s tab.

The next thing you want to do is make sure that you have the correct month. Go to the first tab called “Week Ending 1” and find a yellow cell at the top of the screen. Replace the date in this box with the first Saturday in the month that you wish to create your schedule. Doing this will not only fill in the dates for that week’s schedule, but will filter down for the rest of the month as well. In other words, you will only have to enter this date once a month. In the attached spreadsheet, I have started this schedule for the month of May.

Now you’re ready to start creating your employee’s schedule. In the example, I’ve created a schedule for three different employees. I’ve entereed their start times, end times and even the amount of time that they will be taking for their lunch break. As you can see, the spreadsheet automatically calculates the hours for that day and even adds them to the running total of hours for the week. The cells marked by the word “Area” can be used for whatever you like. In the banking world, I used this as a way to determine if a teller was working in the “lobby” or in the “drive-through”. I also mark in this box if the employee is off work, on vacation, or at a special meeting during the day.

Finally, you can use the “lunch” cells to keep track of vacation hours or personal day requests. For instance, in the example, employee 3 is on vacation all week. Even though they are not working, they still get credit for 8 hours each day. By simply putting a negative number in this cell, the spreadsheet still counts those hours towards the running total. This is very handy for when an employee takes a personal day during the week and you still need to make sure that he/she doesn’t go over 40 hours.

So that’s the spreadsheet in a nut shell. I hope that you find it as helpful as me and my former colleagues did; you might be surprised how much time this can save you once you learn how to use it properly. If you have any questions, please do not hesitate to contact me. Have a great week everybody!

Schedule (Blank)


~ by Scott L. Clark on May 2, 2011.

One Response to “Excelling at Scheduling”

  1. Thank you so much for sharing this Scott. I didn’t need it for scheduling employees, but did need it for a large volunteer schedule for an event. I am not an Excel whiz like yourself and was very grateful to be able to adapt this for my needs.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: