In the work schedule example, 8-hour shifts apply.If on row 42 under Peter, you add a further employee, the formula should be =COUNTIF(C7:I30 C42) etc. This tells the table calculation program: If in the cell fields C7 to l30 the entry from C35 (Anne-Marie) then it should be counted. For the employee Anne-Marie, this will be as follows: =COUNTIF(C7:I30 C35). Excel calculates the number of “ entries” with the COUNTIF function.If you’ve entered additional names into your employee list, then you must complement the related calculation formula. Depending on the gross hourly wage that you enter for Anne-Marie, Excel will calculate the gross income for Anne-Marie in the final column. For example, if you want to assign your employee Anne-Marie to a shift, then this will be automatically assigned and her weekly hours will be automatically calculated (in the example 4 entries each at 8 working hours equal a total of 32 weekly hours). The color-highlighted calculation module on the margin of the Excel template simplifies the shift schedule for you by automatically calculating the number of entries, the weekly hours, and the gross income of your employees. We’ll tell you how this works in the following steps. Now you have to adapt the calculation module in the bottom of the Excel template. You have now created a new drop-down menu that links to your employee list, making the shift allocation process significantly easier. Next, in the “ input message” tab, remove the check next to “show input message when cell is selected.” Do the same with the tick at “ show error alert after invalid data is entered” in the “ error alert” tab.Alternatively, you can enter the source manually. This will now be used as a source for your drop-down menu and will be shown the respective dialog field with the formula, which could look something like this: =$C$35:$C$42. With your mouse, mark your employee list.Now, click on the button next to the dialog box “ source.”.Remove the check at “ ignore blank” but keep the check for “in-cell dropdown.”.Under “settings,” select “ list” in the “allow” drop-down.Click on the “ validation” button in the menu ribbon under “data” and a context menu will open.Now click on any empty cell in the work schedule.Note that the sequence will be mirrored in the drop-down menu. Write out the names of your employees under the “employee list” column. Should you need more employees than the number included (7), then you must amend the drop-down menu. In this way you can also change the names of the sample employees with those in your company. When you change the names in the list, the names in the drop-down menu will adapt accordingly. In this way, you don’t have to manually type in the employee names for the shift each time.Įvery drop-down menu is linked to the employee list in the lower end of the template. Click on it, open the drop-down menu, and select the employees assigned to the shift. Click on the name in the weekly plan and you’ll notice that next to each cell is a small arrow. Or you can follow the following steps.Īs you can see, some sample names have already been entered in the work schedule template. If you do have to make changes, then you should make yourself familiar with the COUNTIF function or the “if then” function in Excel. Only then can the calculation module correctly calculate the shifts, weekly hours, and wages. We recommend, however, that you keep the column segmentation as it is. Of course, it’s up to you to adjust the layout of the work schedule template. The right side of the template offers plenty of white space: for example, for additional notes or to loosen up the work schedule with additional columns. Different fonts and font sizes can also complement this. Use the “fill color” button in the “start” menu to clearly define layers and tables from one another. However, if the work schedule isn’t to your liking aesthetically, then you can easily adapt it – for example by changing the column width. The downloadable Excel template fulfils all requirements of a professional work schedule: It’s limited to the most important information and is designed in a clear and appealing way.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |