We can perform **employee scheduling algorithm** in Excel using the in-built Solver feature. This algorithm is particularly handy when it comes to efficient workforce management for an organization.

## Download Practice Workbook

You can download and practice this workbook.

## Step by Step Procedures for Employee Scheduling Algorithm in Excel

We will perform an **employee scheduling algorithm** for a hospital. First, we have to create a dataset. Then we will create a result outline and insert a suitable formula. And, lastly, we will carry out the analysis with the Solver feature.

### Step 1: Assumption for Employee Scheduling Algorithm

For conducting the **employee scheduling Algorithm**, we must make some assumptions.

- Letâ€™s take a dataset of five nurses and the number of hours they are willing to work each day. Also, they might not work every day.
- In row number 10, we have put the minimum number of nurses required for each day.

- Now, letâ€™s modify this dataset.
- Create a data table of nurses and set
**0**in every cell like the image below.

- This hospital we are working with provides a minimum two holidays for their employees.
- Add a column and a row like this below image.
- In
**Column J**we will find the total number of days each employee has to work in a week. - And, the
**Row 17**for the number of employees required each day. - For this analysis we also have to insert an
**Objective**box to show the total number of employees working hours in a week.

**Read More: How to Create Betting Algorithm in Excel (with Easy Steps)**

### Step 2: Assigning Formula for Employee Scheduling Algorithm

For this analysis we have to set some formulas. We are going to use** SUMPRODUCT** and** SUM **functions as formulas.

Letâ€™s see them.

We want to find out the maximum possible working hour of the employees in a week.

- So, write the following formula in
**K5**and press**ENTER**.

`=SUMPRODUCT(C12:I16,C5:I9)`

Here,

**C12:I16**= array1**C5:I9**= array2

Upon entering the formula, the cell should give **0**.

- Now we will use the
**SUM**formula so that we can find the number of days each employee has to work in a week. - So, write this following formula in
**J12**and press**Enter**.

`=SUM(C12:I12)`

Here,

**C12:I12** = Data Range

- Then
**Hold**and**Drag**the**J12**cell downward to copy this formula for all the cells.

- Again, we have to again set
**SUM**formula in**Row 17**so that we can find the number of nurses working each day. - So, write the following formula in
**C17**and press**ENTER**.

`=SUM(C12:C16)`

Here,

**C12:C16** = Data Range

- Then
**Hold**and**Drag**the**C17**rightward to copy the formula in all the cells.

Thus, our dataset is ready for analysis.

**Read More: How to Use Fuzzy LOOKUP Algorithm in Excel (2 Easy Examples)**

### Step 3: Executing Solver Feature

Now letâ€™s analyze the dataset with the in-built Solver feature.

- First, go to the
**Data**ribbon and click on the**Solver**icon.

So, a **Solver Parameters** window will open.

- Now we have to set these solver parameters which is
**K5**in this case. - Set the Objective cell in the Set
**Objective**box. - Keep the
**To**section**Max**. - And insert the data array
**$C$12:$I$16**in the**By Changing Variable Cells**box. - Also, we have to set three constraints.
- So, click on
**Add**.

- This constraint is for finding the employees working days. We want to know whether an employee works on a given day or not. We want to get the value in binary.
**1**will mean they have to work and**0**will mean their holiday. - So set the
**Cell Reference**to**$C$12:$I$16**and choose the**bin**(binary) option from the adjacent box and press**OK**

- The constraint will appear in the
**Subject to the Constraints**box. - Now, click on
**Add**for another constraint.

- This constraint is for the number of employees required each day which will be equal to the data range
**C10:I10**. - So, set the data range
**$C$17:$I$17**in the**Cell Reference**box. - Then choose the
**=**(equal) sign in the adjacent box. - And, set
**$C$10:$I$10**in the**Constraint box**and press**OK**.

- The new constraint will show up in the box.
- Now, we will add the last constraint.
- Click on the
**Add**option.

- This constraint is for setting the maximum working days for each employee in a week.
- So set the data range
**$J$12:$J$16**in the**Cell Reference**box. - Choose the
**< =**(less or equal) sign in the adjacent box - And lastly set the
**Constraint**to**5**and press**OK**.

- Thus, we have set all the constraints.
- Now, choose the
**Simplex LP**option from the**Select a Solving Method**box and click on the**Solve**button.

- Doing so, the
**Solver Results**window will open. - Make sure the
**Keep Solver Solution**is active and press**OK**.

- Therefore, the solver will give you results.
- The
**Objective Cell**indicates the number of working hours that the organization can get out of its employees. - The data range
**C12:I16**shows the working days of each employee in a binary form. - The data range
**J12:J16**shows the number of working days in a week for each employee. - The data range
**C17:I17**shows the number of nurses assigned on each day.

## Conclusion

Thank you for making it this far. We have shown you how to perform **employee scheduling algorithm** in Excel. Hope you find the content of this article useful. If there are further queries or suggestions please do mention them in the comment section.