This guide explains how to create a timesheet to calculate salaries in Microsoft Excel. You can do this on both Windows and Mac systems, using a predefined template or creating one from scratch.
Steps
Method 1 of 2: Using Templates
Step 1. Open Microsoft Excel
The icon for this program is dark green with a white "X".
Step 2. Click on the search bar
You will see it at the top of the Excel window.
On Mac, click on first File in the upper left corner, then click on New from model … in the drop-down menu that will appear.
Step 3. Type timesheet in the search bar, then press Enter
This will search for timesheet templates in Microsoft's database.
Step 4. Select a template
Click on the one you want to use. The preview page will open, where you can see the format and appearance of the template.
If you don't like the template you have selected, click on X in the template preview window to close it.
Step 5. Click Create to the right of the template preview
This way, you will create a new Excel sheet with the chosen settings.
Step 6. Wait for the template to load
Usually, this will take a few seconds. At the end of the operation, you can start filling out the attendance sheet.
Step 7. Enter all necessary information
Each model has unique characteristics, however you will always have the possibility to enter the following data:
- Hourly compensation - indicates the sum due to the employee in question for each hour of work;
- Employee identification - that is, the name, identification code and other information about the employee.
Step 8. Enter the hours worked in the appropriate column
Most timesheets have days of the week listed in the far left column of the page, so you will need to enter your working hours in the column called "Hours" (or something similar) to the right of that "Days".
For example: if an employee worked 8 hours on a Monday, during the first week of the month, look for the "Monday" cell in the "Week 1" column and enter 8
Step 9. Check the results
All timesheet templates calculate the total hours entered and, if you entered an hourly wage, the total compensation earned by the employee will also appear.
Step 10. Save the timesheet
To do it:
- If you use Windows, click on File, then on Save with name, click twice on This PC, click on a save path on the left side of the window, type the name of the document (eg "Attendance sheet January") in the text field "File name", then click on Save;
- If you use the Mac, click on File, then on Save with name…, type the name of the document (eg. "Attendance sheet January") in the "Save as" field, then select a saving path by clicking on the "Where" field, then on a folder and finally on Save.
Method 2 of 2: Create a Time Sheet Manually
Step 1. Open Microsoft Excel
This program icon is represented by a white "X" on a dark green background.
Step 2. Click on New Spreadsheet
You will see this white icon at the top left of the "New" Excel page.
Skip this step on Mac
Step 3. Enter the column titles
Type them in the following cells:
- In A1 type Day;
- In B1 type Week 1;
- In C1 type Week 2;
- If necessary, add Week [number] in the cells D1, E1 And F1;
- If overtime is expected, you can add the Overtime column to the cell C1 for Week 1, in the cell E1 for week 2 and so on.
Step 4. Enter the days of the week
In the cells ranging from A2 to A8, enter the days from Monday to Sunday in order.
Step 5. Add the salary
Type Salary in the cell A9, then enter the hourly rate in the cell B9. For example, if the pay is € 15 per hour, type 15 in the cell B9.
Step 6. Add the "Total" row
Type Total in the cell A10. The total hours of work will appear here.
If you've also entered overtime, type overtime in the cell A11 and pay for overtime in the cell B11.
Step 7. Enter the formula for the first week
With this formula you will add the working hours from Monday to Sunday, then you will multiply the sum by the hourly wage. To do it:
- Click on the Week 1 "Total" cell, which should be B10;
-
Type
= sum (B2: B8) * B9
- and press Enter.
Step 8. Enter the formula for the other weeks
To do this, just copy the formula you entered for the first week, paste it in the "Total" row under the desired week and replace the part B2: B8 with the letter of the corresponding column (ex: C2: C8).
-
If overtime is expected, you can use this formula to calculate it, substituting the value B9 with B11 - for example, if the "Overtime" of Week 1 is found in the column C., type
= sum (C2: C8) * B11
in the cell C10;
-
If overtime is planned, you can create the "Final Total" section by typing Final Total in the cell A12, inserting
= sum (B10, C10)
in the cell B12 and repeating this operation for all columns of the type "Week [number]" with the corresponding letters.
Step 9. Fill out the time sheet
Enter the working hours for all days in the "Week 1" column. You should see the hours and total salary due at the bottom of the sheet, in the "Total" section.
If overtime is planned, fill in this column as well. The "Final Total" section will change based on the sum of your normal and overtime wages
Step 10. Save the timesheet
To do it:
- If you use Windows, click on File, then on Save with name, click twice on This PC, then on a save path on the left side of the window, then type the name of the document (eg "Attendance sheet January") in the text field "File name", then click on Save;
- If you use the Mac, click on File, then on Save with name…, type the name of the document (eg "Attendance sheet January") in the "Save as" field, select a saving path by clicking on the "Where" field, then on a folder and finally on Save.
