How to Create Excel Template for Shift Arrangement Easily?
Can we make use of Excel function such as "Substitute", "Len", "Find", "Indirect", "Countifs" and Conditional Formatting?
Step 1: Spare a Column for Staff Code
For example, fill in the staff code in the column on the left:
Step 2: Define Time Period of each Shift in each Day
Time period of shift is defined in the column on the left.
Then, fill in the staff code under the corresponding day and shift.
Step 3: Count Number of Staff Show Up for Each Shift
In step 2, each staff code is separated by the comma “,”. Therefore, we can count the number of comma “,”, in order to check the number of staff in each shift.
We can use the following Excel function:
- SUBSTITUTE: Using this formula to remove the comma
- LEN: Check the length of the text string
The difference in length of text string, before and after removing the comma, represents the number of comma in the original text.
Step 4: When will each Staff on Duty?
Using the following Excel function to check whether a particular staff code appears in each shift that is filled-in in step 2.
FIND: Check if a particular text string appears in the Excel cell
INDIRECT: Point to the cell according to the location we input
Step 5: Summarize Number of Shift applicable to each Staff
Using "COUNTIFS" to count the number of shift that each staff member show up:
If we want to highlight staff member who shows up for four shifts or more, we can use “Conditional Formatting” in Excel:
Fill in the colour we want for those greater or equal to 4.
Download Our Excel Template for Your Reference:
To make your HR work more easily, you can also refer to the following blogs regarding the use of Excel.
Use Excel to Calculate Salary, MPF or Conduct Appraisal.
Use Excel to maintain Annual Leave Balance
Share with your friend if the above is useful to you!