Features

Spreadsheet Staffing

Computer algorithms can create schedules with the right software and enough time

View Comments (2)Print ArticleEmail Article
Section Sponsored by:
http://www.asct.com

When Sharp launched the first battery powered handheld calculator in 1969, it's a safe bet we didn't expect it to consume our time. When scheduling employees, a lab manager might have used it to add number of shifts worked, calculate percentage of overtime worked, etc. If a hospital was willing to spend $395 (over $2500 today)1,2, it would have saved him time.

In the last forty four years calculators have evolved into computers that clutter our desks and consume much of our time. But creating an employee schedule hasn't changed, whether fussed over with pencil and paper or typed into a spreadsheet program. By understanding and applying two spreadsheet functions, you can make the computer work for you, saving you time and effort. 

Computer Scheduling 
Computer algorithms can create schedules with the right software and enough time. Complexity theory classifies these kinds of problems as "nondeterministic polynomial time" problems (abbreviated NP)3 because they are optimally solved through trial and error over a period of time proportional to the size of the problem. Computers can use genetic algorithms to systematically prune a schedule. But a lab manager is usually much quicker, because the search space of possible solutions is familiar territory.

Lab managers are prone to bias in testing the fairness of a schedule, however. A computer doesn't care about perceived slights, favoritism, or who is more agreeable, and employees know it. As one scheduling software vendor puts it, "employees love the fact that ... the scheduling algorithm ensures employees receive equal treatment over the most prized shifts."4

Fairness in scheduling relieves stress in the workplace. Organizational psychologists believe that employees are motivated by morality and ethics (how fair is the schedule to my coworkers?) as well as selfish interests (how fair is the schedule to me?). This extends to the broader cultural impact of an organization -- how its business is conducted -- and ultimately affects employee behavior. "A sense of injustice," says organizational psychologist Deborah Rupp, "may spark hostility, aggression, counterproductive behaviors, absenteeism, and even quitting one's job."5

The good news is even if you can't fit a computer scheduling program into your department budget, just two spreadsheet functions can still help make your schedule more fair.

Spreadsheet Functions 
Here are two functions that work in most spreadsheets, including Microsoft Excel: 

  • IF( this is true, do this, otherwise do this ) - this is a conditional function that is useful for modifying a cell content on the fly.6
  • VLOOKUP( value, table array, index, [not exact match] ) - this isn't as scary as it looks; the "vertical lookup" function searches a column for a value, looks index number of columns over, and returns that value.7

Don't be scared by the geek speak. VLOOKUP is incredibly useful when dealing with dynamic tables, such as an employee schedule.

If This, Do That
Consider a simplified day shift employee roster as in Table 1. Shift times are assigned according to a template with the least desirable 9-5:30 shift swapped with the 8:00 AM shift on a rotating basis. All that remains is assigning the core laboratory workstations.

TABLE 1: A Simplified Employee Roster

fx   =if(C3=6,"Chem","")

fx

A

B

C

D

E

F

G

H

1

 

 1

2

3

4

5

6

7

2

 

Sun

Mon

Tues

Wed

Thurs

Fri

Sat

3

Mary

6

6

 

6

6

6

 

4

Jane

 

7

7

7

 

7

6

5

Sue

 

9

6

8

9

8

 

6

Mike

 

8

9

8

7

9

 

7

Sam

 

8

8

9

8

8

 

8

 

 

 

 

 

 

 

 

9

Mary

 

 

 

 

 

 

 

10

Jane

 

 

 

 

 

 

 

11

Sue

 

 

 

 

 

 

 

12

Mike

 

 

 

 

 

 

 

13

Sam

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

16

 

 

 

 

 

 

 

 

17

 

 

 

 

 

 

 

 

18

 

 

 

 

 

 

 

 

19

 

 

 

 

 

 

 

 

20

 

 

 

 

 

 

 

  


For different shift times that are always assigned, IF can do the heavy lifting. Enter the following formula into cell C9 (in Excel, press F2; all formulas begin with an equals sign) and copy and paste across the row:

=if(C3=6,"Chem","")

What this IF does is simple: it assigns Chemistry to whoever works the 6:00 AM shift to get instruments online, quality control checks done, inpatient work completed, etc. When you copy and paste, the software automatically updates the cell reference.

What about the 7 AM shift? IF functions can be nested to combine options. Substitute the following:

=if(C3=6,"Chem",if(C3=7,"Micro",""))

Instead of assigning Chemistry OR leaving the cell blank, this IF adds an option to assign Microbiology if the time is 7:00 AM instead of 6:00 AM as in Table 2. Nested IF functions are powerful, but they can only be nested seven deep.

TABLE 2: Chemistry and Microbiology Assigned Using IF

fx  =if(C3=6,"Chem",if(C3=7,"Micro",""))

fx

A

B

C

D

E

F

G

H

1

 

 1

2

3

4

5

6

7

2

 

Sun

Mon

Tues

Wed

Thurs

Fri

Sat

3

Mary

6

6

 

6

6

6

 

4

Jane

 

7

7

7

 

7

6

5

Sue

 

9

6

8

9

 

6

Mike

 

8

9

8

7

9

 

7

Sam

 

8

8

9

8

8

 

8

 

 

 

 

 

 

 

 

9

Mary

 

Chem 

 

Chem

Chem

Chem

 

10

Jane

 

Micro

Micro

Micro

 

Micro

 

11

Sue

 

 

Chem

 

 

 

 

12

Mike

 

 

 

 

Micro

 

 

13

Sam

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

16

 

 

 

 

 

 

 

 

17

 

 

 

 

 

 

 

  

18

 

 

 

 

 

 

 

 

19

 

 

 

 

 

 

 

 

20

 

 

 

 

 

 

 

 


Lookup Power
Given a variety of shift times, scheduling departments could be easy. But if you want to assign by different criteria to the same shift time, VLOOKUP does the job. In this case, we want to assign Hematology to an 8:00 AM shift according to a sort order. For the sake of simplicity we'll make it alphabetical.

TABLE 3: Shifts Sorted Using VLOOKUP

fx  =if(vlookup($A16,$A$3:$H$7,C$1+1,0)=8,sum(C$15:C15)+1,"")

fx

A

B

C

D

E

F

G

H

1

 

 1

2

3

4

5

6

7

2

 

Sun

Mon

Tues

Wed

Thurs

Fri

Sat

3

Mary

6

6

 

6

6

6

 

4

Jane

 

7

7

7

 

7

6

5

Sue

 

6

6

8

9

8

 

6

Mike

 

9

9

8

7

9

 

7

Sam

 

8

8

9

8

8

 

8

 

 

 

 

 

 

 

 

9

Mary

 

Chem

 

Chem

Chem

Chem

 

10

Jane

 

Micro

Micro

Micro

 

Micro

 

11

Sue

 

 

Chem

 

 

 

 

12

Mike

 

 

 

 

Micro

 

 

13

Sam

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

16

Jane

 

 

 

 

 

 

 

17

Mary

 

 

 

 

 

 

 

18

Mike

 

1

 

1

 

 

 

19

Sam

 

2

1

 

1

1

 

20

Sue

 

 

 

2

 

2

 

Add rows to the spreadsheet with the techs listed alphabetically as in Table 3 and enter the following formula into cell C16:

=if(vlookup($A16,$A$3:$H$7,C$1+1,0)=8,sum(C$15:C15)+1,"")

Copy and paste this into all cells for the week, and the 8:00 shifts are numbered from above. This VLOOKUP looks for the name in the leftmost column, counts C$1+1 (the day of the month) columns across, and enters the sum of the column above plus 1. Note the "$" cell designation that makes the references absolute; these won't change when copied. See Table 4.

Next, add a similar VLOOKUP to our original formula in the department assignment grid, which looks like this:

=if(C3=6,"Chem",if(C3=7,"Micro",if(vlookup($A9,$A$16:$H$20,C$1+1,0)=1,"Hema","")))

TABLE 4: Hematology Assigned Using VLOOKUP

fx  =if(C3=6,"Chem",if(C3=7,"Micro",if(vlookup($A9,$A$16:$H$20,C$1+1,0)=1,"Hema","")))
fx A B C D E F G H
1    1 2 3 4 5 6 7
2   Sun Mon Tues Wed Thurs Fri Sat
3 Mary 6 6 6  
4 Jane 7 7 7   7 6
5 Sue 9 6 8 9 8  
6 Mike 8 9 8 7 9  
7 Sam 8 8 9 8 8  
8                
9 Mary   Chem   Chem Chem Chem  
10 Jane   Micro Micro Micro   Micro  
11 Sue     Chem        
12 Mike   Hema   Hema Micro    
13 Sam     Hema   Hema Hema  
14                
15                 
16 Jane              
17 Mary              
18 Mike   1   1      
19 Sam   2 1 1 1 1  
20 Sue       2 2  

Since the 8:00 AM shifts are sorted alphabetically and numbered 1 or 2, all this function has to do is add a VLOOKUP to find the number 1, the first person alphabetically who works an 8:00 shift for that day. Finally, highlight the behind the scenes rows that do all the grunt work, right click, and Hide them (don't worry, you can unhide them).

Up front time in creating such a spreadsheet is worth the effort, since future schedules will assign departments automatically, saving time. You (and your techs) won't have to think about why a decision is made, because a computer doesn't have a bias. Your team will have more energy for other important tasks, such as improving patient care.

Scott Warner is laboratory manager, Penobscot Valley Hospital, Lincoln, ME.

References

  1. Wikipedia. Sharp QT-8D. Available at: http://en.wikipedia.org/wiki/Sharp_QT-8D. Last accessed: 12/23/13.
  2. Bureau of Labor Statistics. CPI inflation calculator. Available at: http://www.bls.gov/data/inflation_calculator.htm. Last accessed: 12/24/13.
  3. Dictionary.com. Nondeterministic polynomial time. Available at: http://dictionary.reference.com/browse/nondeterministic+polynomial+time. Last accessed: 12/24/13.
  4. Hakuna Staff Scheduling Software site. Hakuna's schedule writing software algorithm. Available at: http://www.hakunaschedulingsoftware.com/algorithm.php. Last accessed: 12/26/13.
  5. Tricoles R. Fairness in workplace key to employee, organizational health. Available at: http://www.fabbs.org/fabbs-foundation/education-resources/science-communications/fairness-at-work-key-to-employee-and-organizational-health/. Last accessed: 12/26/13.
  6. Techonthenet.com. MS Excel: if function (ws). Available at: http://www.techonthenet.com/excel/formulas/if.php. Last accessed: 12/27/13.
  7. Techonthenet.com. MS Excel: vlookup function (ws). Available at: http://www.techonthenet.com/excel/formulas/vlookup.php. Last accessed: 12/27/13.

 


 

At StaffReady, we have found there is a profile for an organization where spreadsheet scheduling works well and when it falls short. We've designed a short quiz to help organizations understand where they are in that spectrum. Great article, thanks!

http://www.staffready.com/take-excel-breaking-point-assessment.html


Nancy Janzen,  CEO ,  StaffReadyMarch 10, 2014
Spokane, WA



looks cool

Mary HupkeFebruary 28, 2014




     

Email: *

Email, first name, comment and security code are required fields; all other fields are optional. With the exception of email, any information you provide will be displayed with your comment.

First * Last
Name:
Title Field Facility
Work:
City State
Location:

Comments: *
To prevent comment spam, please type the code you see below into the code field before submitting your comment. If you cannot read the numbers in the below image, reload the page to generate a new one.

Captcha
Enter the security code below: *

Fields marked with an * are required.

 
http://www.advanceweb.com/LabManagerACO
http://shop.advanceweb.com/clearance.html
http://www.advanceweb.com/jobs/search/jobview/416337/medical-technologist.html?jobb=#forward
http://www.quickslide.com
http://events.advanceweb.com/Attendee/Default.aspx