Link: go back to previous page

The Clockworks project

Clockworks is the name of a scheduling program I wrote in Visual Basic.NET for Shop N Save, where I worked at the time. I came up with this idea on my own because the company did not have any software to assist department heads with making schedules. They worked directly off sheets of paper, and the process was difficult and time consuming.

This page is divided into sections below (the list items are hyperlinks):

  1. Problems with using the previous method
  2. Goals and restrictions of the software
  3. Example images from the help files
  4. Software file format
  5. Overall projections of cost savings
  6. Final software status

Problems with using the previous method

The cashiers department was the largest of the store, containing roughly 1/3 of all the store employees. At the store I worked at, there was about 50 cashiers at the peak. All of the schedules were created by hand on sheets of paper, but the way this schedule was created weekly was particularly surprising:

  1. A specially printed pad of blank schedules had to be ordered as a store supply, because it was an extra-long size (Legal @ 11 × 14 inches). A normal-sized sheet of paper would not have been long enough for many stores to list all the cashiers that they employed.
  2. All the names of the ~50+ cashiers names had to be hand-copied onto this page, in order of seniority: full-time employees listed first, and then part-time employees. A separate section listed office cashiers (a semi-management classification, for cashiers who had special training to do daily accounting tasks) by seniority also.
  3. The cashiers department had sales projections for each day of the week, and the cashiers' schedule was supposed to reflect those projections by proportionately assigning hours to each of the days, for both the regular cashiers and the office cashiers.
  4. Different days of the week would see maximum store traffic at different times. There were charts provided by the company that showed the hourly sales averages, and the written schedules were supposed to mirror this traffic distribution throughout the day.
  5. There was only a paper notebook to keep track of different cashiers availability schedules. To reserve a particular day off, they had to submit a written note that was saved in a pile until the schedule was made. The written-note requests for days off could only be submitted in the week previous to the scheduled week, since the store didn't want the problem of trying to keep different piles of notes separate. They would not schedule any employee days off more than a week in advance just due to the inconvenience of it, except for extreme cases.
  6. The department managers (for each of the separate departments of the store, such as cashiers, produce, meat, etc.) had to key these paper schedules into a computer program after the schedules were completed, but the computer offered no feedback or analysis of the schedules. So it was normal for the cashiers schedule to need revising after it had been posted, since someone had been scheduled when they had requested off, or when they were not available to work at all.
  7. All this was written on a sheet of paper, and several photocopies were made for posting in different locations around the store. At the end of the week when the next schedule had to be made, all the requests for days off were considered, a copy of the schedule had the days that needed changing covered with white-out, those days/persons schedules were re-written, and that schedule was re-copied again for posting around the store.
  8. After about four weeks, the whole cashiers schedule had to be completely re-copied onto a new sheet of paper because with all the white-out and re-writing, it was no longer readable after photocopying several times. They put this off as long as possible due to the time that re-copying it by hand would take.
  9. The head cashier would usually spend roughly 3 hours a week making the initial schedule, plus perhaps a half-hour longer making revisions after the new schedule was posted. Common causes of revisions were scheduling employees when they were not available, scheduling them when they had requested off and not scheduling enough cashiers during times that the store manager felt would be peak business hours.

Link: back to top of page

At some point I became curious about the possibility of writing a program to help make these schedules.

Goals and restrictions of the software

  1. This program had to be quick and easy to use, for the departments that didn't have many people and didn't have any complex scheduling requirements.
  2. It had to be able to help with all major aspects of creating the cashiers schedule, since that was the major time sink.
  3. An additional condition was that this program had to be able to run entirely from the local machine that it was on. I did not expect that the company would allow me any use of local or remote servers on the private network, and this way the program could exist alone on a separate laptop if necessary. The only issue after that was printing the schedules, but they could be 'printed' to xps files and then transferred with a thumb drive to one of the store's computers, and could be printed from there.

Link: back to top of page

Note: the images below were taken from the help files for the program, so they have arrows and informational boxes added that are not completely explained in the descriptions here.

Example images from the help files

I authored a set of HTML files to provide written help. The program's main window was a tabbed pane, and each tab had an orange button that would call the help file page for the page that the button had been clicked. These images are screenshots of those tabs.

Image #1: Below was the main tab visible upon program start-up. In order to edit schedules, a user name and password were required. The logon credentials were unique to this program (they were not related to any other company logins). The program user accounts had many aspects of program use separately controllable by different permissions.

Image #2 below: This was the main schedule editing tab.

The store where I worked had pretty old monitors that didn't have a real great resolution, so this program had to be designed with a user-interface to fit into a 1200x800-pixel screen. The large open gray area (labeled below as number 1) is where a schedule chart would be shown, once it was loaded.

Images #3 and #4 below:
(These images are shown full-width below but they are fitted to the screen height of the program. When using the program, you must scroll left and right to see them completely)
The upper image is showing a weekly schedule, and the lower image is showing a single day schedule. The group of columns off to the right is a number of automatic error-checking functions that were performed. Click on either image for a larger view.

There are some features that are not shown or not obvious in the two charts above.

One feature was that it was possible to place notes on individual employee rows of the chart, at any time or day. The note looked like a little amber square, like a "Post-It" note. Clicking on the note would reveal what it said, and allow further editing. Using these notes, it is easy to mark future dates for any reason, with a text explanation of the subject matter.

I also noted before that the company didn't have any good way to track employee availability. This program had a feature for setting what hours an employee was available to work, and then that information would be displayed as a colored horizontal bar near the bottom of their row on the schedule.

Later on I added a second feature that showed the employee's preferred working hours as a range of three more different colors, and this also was shown under the availability bar. Both of these features made creating schedules on the fly much easier.

These two bar charts were eventually added to most of the other charts, so that when editing with any chart, it was possible to see when any employee wasn't available, and also to see the times of each day that they would prefer to work.

Image #5 and #5a below:
The program had a number of chart methods for visualizing the distribution of employee hours while editing the schedule.

One of those was the profile charts. These charts showed the distribution of employees as a line chart. Experimentation showed that there was only enough resolution on the screen to display four different lines well, but as it happened there were never more than three groups of employees to show on any one chart—leaving the last line free to serve as the total-count indicator.

The colors of the lines varied depending on the circumstances of the chart. In the examples below, this chart is displaying a schedule for the cashiers department and the color key is as follows: red = head cashier, violet = office cashiers, green = regular cashiers and the black line indicates the total of all types. The four lines were slightly offset horizontally and vertically to try to prevent them from over-writing each other.

These charts are displayed and hidden by a rolling the mouse cursor over the chart's icon panel. If no day is selected, the week chart is shown; if a single day is selected then the chart for that single day is shown.

Image #5:

Image #5a:

I should probably explain here that this company used 10:00 PM as the change-over time for scheduling. So each new day of the schedule begins at 10:00 PM the night before, and ends at 10:00 PM on the current day. A light red block represents the time that shifts on the current day or week can overhang onto the next day.

Image #6 and #6a below:
These charts also later had an "auto-size" variant made. While making test schedules I found that it was rare that any day's schedule used more than 1/3rd of all the employees, so the charts left a lot of unused space on the screen. The auto-size variants were exactly the same except that the scale automatically adjusted to try to fill the whole screen better.

Image #6:

Image #6a:

Image #7 below:
These charts also had a "block" mode that would total the numbers of each type of employee present each hour, and color them in order of priority, from the bottom up. In the chart below, the red blocks represent when the head cashier was working, the violet blocks represent hours that the office cashiers are working, and the green blocks represent hours that the regular cashiers are working.

Image #8a, #8b and #8c below:
The profile charts were an enormous help in arranging schedules that had a lot of shifts, but while using them I found that there was a certain problem that they couldn't help with. Sometimes during arranging the shifts on a schedule, there would be small "gaps" in the profile chart, that represented when one shift ended, and another one began a short time after. Using the other two charts, it could be very difficult to find out what two shifts were causing that gap.

Image #8a below:
The profile chart below shows where one of these gaps exists. It shows it in the black line, which is the overall chart total. And it shows the gap present in the green line, which shows that it exists between two tier-3 (regular cashier) shifts. What it cannot show is which two shifts are causing the issue.

Image #8b below:
The gap-view chart is shown below. This chart is different than the others before, in that it draws a block which represents each employee shift, and the number on the block represents what row of the regular schedule chart the shift exists on. The block must all be drawn on the same row of the chart, and this chart arranges all the blocks to minimize the gaps present between any two blocks. The blocks are drawn using the same colors that the lines use in the profile charts: red for tier-1 (the head cashier), violet for tier-2 (the office cashiers) and green for tier-3 (regular cashiers).

Note: the smallest time interval allowed with this program is 15 minutes, because that is the interval that the company used.

In the image below, shift #23 is circled in red, and behind that shift is a 15-minute gap before shift #38 begins.

Image #8c below:
The gap-view chart instantly shows where gaps exist between shifts, that cannot be easily resolved with the other charts. In this circumstance, the shifts usually begin on the hour or on the half-hour. Shift #23 has been placed incorrectly, and it begins on the 45-minute mark. The gap-view chart only allows one type of editing: the shift blocks on the gap-view chart can be dragged left and right to change the start times of the shifts. So if you were to drag shift #23 slightly to the right, you would change its start time from 9:45 AM to 10:00 AM, and the gap in the schedule profile chart would be resolved.

Image #9 below:
The mini week-view chart was made to provide an overall way to visualize the entire chart. With the regular week-view and day-view charts, only about 14 rows (employees) could be shown at a time. Beyond that, there was no way to see the entire chart at once. This chart doesn't allow any editing, and it doesn't show many details. It does allow getting an overall placement of the shifts, and when any row was clicked on, it would display who that row represented in the main charts.

Image #10 below:
The days-off-view chart is a supplementary view of the main chart.

It was common at this company that many people didn't like working long stretches of multiple days at a time, and the department managers tried to avoid it as well as possible. This chart was made to help with that: it instantly shows where employees are working more than 4 days at a time.

This chart shows the entire schedule, with each row representing one employee on the schedule. It also shows three weeks: the current week as the center section, the previous week as the left third, and the coming week as the right third.

The colored boxes represent a day on which that employee has a work shift. The boxes are color-coded by how many boxes exist contiguously before that person has a day off. The 'color guide' section tells the values of the colors used.

This chart is also interactive in that if a single row was clicked on, it would tell you what row that was, and person that row represented on the main chart.

Image #11 below:
The program also has a view-only tab.

The view-only tab looked very similar to the edit-tab, but with two big differences:
1. The view-only tab did not allow editing schedules (this is why some of the controls necessary to that are omitted). And-
2. The view-only tab worked without a user logon. This way it was possible for anyone to start the program and view any schedule that existed, on-screen.

If the user was logged in and was allowed to edit schedules, then the view-only tab could be used to copy from and paste shifts into the schedule that was open in the edit tab.

Image #12 below:
The program has a concept for scheduling called 'target hours'. The schedule is given an overall amount of target hours, that represents how many hours that the entire department can use for the week. Then on another tab, a separate chart allowed easily setting each individual employee also has an individual target hours value, so that the total hours given to all the employees was the same amount given to the department for the week. The image below shows the employee target hours tab, which is for setting the individual hours targets for each employee.

The employee target hours is displayed in the main edit tab charts. This way, if a manager wished to lay out employee shifts by hand, the edit chart provided a running total of how many hours each employee was scheduled, and it also showed the target hours value allotted for that employee.

Image #13 below:
The image below shows a feature designed to help with part of the complications of the cashiers' schedule.

With the cashiers' schedule, the department is provided with seven different target hour values, for each day of the week. The head cashier must use these hours on the days provided. If someone creating a schedule just randomly places shifts around on the different days, they aren't likely to meet the individual daily targets. So there is two different ways to try to match the hours used on each day to the hours assigned: either alter individual employee shifts, or change the days that employee shifts are placed on. This window is for adjusting an individual's shifts.

The top panel shows some figures about the total hours of the schedule. Sunday, Monday, Tuesday and Wednesday have too many hours assigned to them. Thursday, Friday and Saturday do not have enough hours assigned to them, the green figures show the hours remaining on each day.

This particular person (Deborah Bareiter) has no shifts at all on Wednesday or Thursday, so her shifts can't be adjusted to help with those differences. But she does have shifts on Monday and Tuesday that can be cut, and she also has shifts on Friday and Saturday that could be increased to help meet the schedule's daily hour targets.

Image #14 below:
It was explained above that there is two different ways to try to match the hours used on each day to the hours assigned: either alter individual employee shifts, or change the days that employee shifts are placed on. This tab is for changing the days of an individual's shifts.

The top portion of this chart has information about the distribution of hours for the entire cashiers' schedule. The first row printed in black that says "Target Hours" is the total amount of hours used (in the left column) and has the amounts assigned to each day in the corresponding column. The next row down says "Hours used" and it displays a running tally of the hours used, both for each individual day, and for the overall total (in the left-most column).

When this chart is used, the shifts can be dragged left or right to change the days that they are on. The start times and lengths are not used, since that would have already been set elsewhere. And by changing the days of different-lengths of shifts, you can change the total hours used on each day. As the shifts are altered, the totals in the upper pane are updated.

This chart example has already been completed, and all of the day totals are exactly on target. That isn't always possible to do just by moving shifts, but in this case it was possible. So all of the day columns say [on target] in the third row down. If any day target was not met, a number is displayed giving the amount of hours that it is over or under the target.

The gray rows represent the ideal distribution of the three employee tier types. In the left-most column is shown the totals, based on the percentage that each tier is of the total. Ultimately the head cashier and the office cashiers are all counted as tier-2 employees, so they can be combined and considered to be a single total for this purpose. And the head cashier is counted as a percentage on all seven days, but they can only be scheduled to work 5 days a week normally.

So the main figure of importance here is the % one for the tier-3 employees. In the left-most column it is given as 76.37% of the total. What that means is that ideally you would want the tier-3 percentage of all seven days to hit as closely to 76.37 as possible, because that is the percentage of all the hours that all of the tier-3 employees were given in the employee-target-hours tab. Most of the days here come pretty close; Sunday and Friday are a bit under and Saturday is a bit over.

(-I recall that I played with this for quite some time when I was making these help files, and this was the closest that I could get this example cashiers' schedule to come to being perfect. If I got the percentages any closer, then the days weren't on-target in total hours. So on any given schedule, it may not be possible to resolve both perfectly. The whole point of this feature is that it is much faster and easier than trial-and error, and much more accurate than just plain guessing-)

Image #15 below:

The last tab was where various less-common tasks could be accessed.

Part of the reason for these different buttons was that the permissions in the user accounts had settings to allow using each of these buttons to open the pertinent windows.

A brief explanation of the visible buttons follows:

In the "Regular Tasks" groupbox:

Store events are special messages that could be assigned dates and times, and that appeared at the top of the on-screen and printed schedules.

Add/delete employees is the utility for creating and deleting employee records.

Print options is options to do with printing hard copies of the schedules. The program is also capable of printing blank schedules, should the need arise.

Data tools is features related to different visualization uses. I put this in but didn't develop it much, as I didn't really know what features would be useful.

Hours transfer list is a utility that had to do with easily transferring hours between different store locations, such as when an employee had to work a shift at another store location.

Open Employee Event List Editor this is a feature that allows editing employee events (shifts) directly using a pop-up window to select them from a list, rather than through the on-screen chart methods (in case there would be a bug with them). I had to creat it when writing the program initially, and decided to leave it in as an additional feature.

View allocation chart this is a chart which showed all employees ranked by seniority, and indicated if their hours were assigned properly. According to union rules, employees were supposed to be scheduled hours strictly by seniority but the individual departments had hours targets as well, and all employees were not capable of working in any department. In my own testing I cound that satisfying both these requirements often wasn't possible, so this chart was hidden by default.

In the "Program Settings" groupbox:

ADS Options is settings related to how the Allocation chart is generated. In ADS, the A stands for Allocation, the D stands for (store) Department and the S stands for Schedules, which is the three levels that are pertinent to the allocation chart. What this had to do with was the way that hours had to be assigned by seniority, according to union rules.

Program Options is various program settings related to general program functioning.

Security Options is settings related to the logon system.

Change user password allows changing one's password, if you are logged on.

In the "Other tasks" groupbox:

Open a non-standard file and-
Save to a non-standard file: with this program, it had a certain folder set as the default to use for storing the schedule files. This was necessary for the program to function but it also meant that if you ever wanted to save one elsewhere or open one in a different location, you had to have a way of telling the program that the file isn't in the usual folder. These buttons called up normal Open-file and Save-file dialogs to allow doing that, as well as disabling some program settings that would fail if used on a file that wasn't in the default location.

View Log-In History as part of the security options, a file could be generated that contained a history of the last few hundred logins, or login attempts.

View EVE history The EVE here is the Employee Event List Editor, as featured in an earlier button. There is a log file that can be generated, tracking who uses it and when. The EVE is perfectly valid to use, but the idea here was that people might resort to using it if the edit-view charts weren't working correctly, or if the edit-view charts were just too difficult to use for some reason. So this way if someone was using the EVE often, you could find out about that and ask them why.

Edit employee events on expired schedules By default, this action was blocked. If you had the permission to do this, then you could click this button and open past schedule files and edit them normally. The reason that this special button is here is to prevent people from editing expired schedules inadvertently.

Reinstate an employee from a past schedule When deleting an employee record, there are two options given. One is to simply delete the record, and the other is to place it into an archive where it is no longer included on the schedules, but the information is still accessible when viewing past schedules. After an employee record has been moved to the archive, this button allows re-converting it back into a normal employee record.

Link: back to top of page

Software file format

Typical business programs use a database file format. I decided against that here for two reasons.

  1. Database formats allow multiple users when accessed from a server, but this program was to run off of local data files. Concurrent users wouldn't be possible due to no server environment.
  2. A software bug could possibly corrupt the entire database file, losing all records. In a server environment normally there would be automated backup, but that ability wouldn't be present here either.

Since the two main advantages of the database format wouldn't be available, I tried to find beneficial uses of an alternate format. The file format I decided on has each week stored in a separate HTML file, and these were grouped in folders by calendar year.

The reason that I chose to use an HTML format was because that way the file could serve two purposes. The weekly schedule format was saved as an HTML file, in such a way that the section used by the program was hidden in comment tags, and a simplified version of the output of the weekly schedule was printed as HTML. This way anyone without the Clockworks software could have viewed the schedule directly, with just a web browser. All they would have needed was to know the location of the file on the company network. They would not have been able to edit it or use any of the special features, but they would have been able to see what would have been printed on the final version of the schedule.

The diagram below shows the overall layout of the schedule files when viewed in an HTML browser. The page layout has three sections, arranged vertically. The first (top) section is a listing of all of the schedules by name, the second section is a listing of all of the employees in alphabetical order, and the third section (the bottom) is a list of HTML tables (one for each different schedule present) showing the posted information in each schedule.

Clicking any of the schedule names in the top section takes you immediately to the beginning of that schedule's table in the bottom section of the page.

There is also a listing of all store employees alphabetically by last name, in case soneone was searching for a particular person and didn't know what store department schedule they were listed on. Clicking any of the employee names in the second section takes you to that employee's listing, in the schedule that is set as that person's default schedule.

Schedule file example: shop_n_save_1773_clockworks_schedule_2016_01_02.html

Link: back to top of page

Overall projections of cost savings

Previously I noted that creating the weekly cashiers' schedule required roughly three to three and a half hours of time.

With this program, I found I could immediately produce a new schedule with considerable changes of 50 employees in less than thirty minutes, and after some practice that time went down to nearly 15 minutes (–the other departments had to be completed also, but they did not involve nearly the difficulties that that the cashier schedule had–).

So it could be fairly assumed that at the minimum this would have a roughly three-hour time savings, one per week, per store. Multiplying that by 52 weeks in a year and by the 45 stores that the company operated at the time, gives a total of 7,020 hours per year company-wide.

A single full-time employee would work about 2000 hours per year, for comparison.

Link: back to top of page

Final software status

Note: this program may not be currently functioning.

I was in the middle of editing the program to change over to what I felt would be a better schedule file system when my PC had a main hard drive failure.

This was also about the time that I presented the first version to a superior, who thought it wasn't compelling enough to try to use. After that I stopped working on it. So I still have at least 90-95% of this code but it may not be the last/best version. I don't exactly recall what edits I was doing at the time that I decided to abandon the failing hard drive.

Link: back to top of page

Link: go back to previous page

[end of page]