Creating a Simple Employee Scheduling in Excel with No Duplicate Shifts

To create a simple employee scheduling using Excel for 3 shifts (Morning, Afternoon, and Night) with 16 employees, follow the steps below:

1. Open a new Excel workbook.

2. Name the columns as follows:

   • A: Employee ID
   • B: Name
   • C: Shift Preference
   • D: Available Days (Monday-Sunday)
   • E: Morning Shift
   • F: Afternoon Shift
   • G: Night Shift

3. Enter employee data in columns A, B, and D.

4. In column C, list the shift preferences for each employee.

5. To assign shifts, use the following formulas:

   • For Morning Shift (Column E):

     =IF(ISNUMBER(SEARCH(C2, "Morning")), IF(ISBLANK(D2), "Available", "Assigned"), "")
     

     Copy the formula down the column.

   • For Afternoon Shift (Column F):

     =IF(ISNUMBER(SEARCH(C2, "Afternoon")), IF(ISBLANK(D2), "Available", "Assigned"), "")
     

     Copy the formula down the column.

   • For Night Shift (Column G):

     =IF(ISNUMBER(SEARCH(C2, "Night")), IF(ISBLANK(D2), "Available", "Assigned"), "")
     

     Copy the formula down the column.

6. To check for overlapping shifts, use conditional formatting:

   • Select the Morning Shift column (E).
   • Go to the Home tab, click Conditional Formatting, and choose Highlight Cells Rules > Duplicate Values.
   • Set the formatting style and click OK.
   • Repeat the process for the Afternoon Shift (F) and Night Shift (G) columns.

7. Manually adjust the schedule to avoid overlapping shifts and ensure each shift has the required number of employees.

Here's an example of the generated HTML output:

<table>
  <tr>
    <th>Employee ID</th>
    <th>Name</th>
    <th>Shift Preference</th>
    <th>Available Days</th>
    <th>Morning Shift</th>
    <th>Afternoon Shift</th>
    <th>Night Shift</th>
  </tr>
  <tr>
    <td>1</td>
    <td>John Doe</td>
    <td>Morning, Afternoon</td>
    <td>Monday, Tuesday, Wednesday, Thursday, Friday, Saturday</td>
    <td>Assigned</td>
    <td>Available</td>
    <td>Available</td>
  </tr>
  <!-- More rows -->
</table>

References:

• Excel IF Function
• Excel SEARCH Function
• Excel Conditional Formatting