Are you looking for an efficient and reliable way to keep track the hours worked and calculate their value. Using this online template you can easily setup your employees, work days, vacations, weekends and hourly rates. This will simplify payroll, keep you organized.
The excel timesheet calculator is available to download online as a weekly, bi-weekly, or monthly online timesheet template to download for free.
This Excel spreadsheet contains 3 timesheet calculators: Weekly, Bi-Weekly, and Monthly timesheet calculator. The fourth sheet, called Masters, is for setting purposes. Let's explain each one of them in detail. Pictures are included with details on how the calculator looks and works. The download link to the XLS file is available towards the end.
Update 1 Nov 2023: We have updated the sheet with the pay periods for 2024, and fixed a bug related to the format in the overtime fields, the formula for overtime pay, and made some visual changes to make the sheet easier to follow, plus a better explanation on how to gain the maximum value from the sheet.
1
The Weekly Time Sheet helps you to manage the amount of work done in a week. It contains data for all the employees in Employee Master. The first thing you need to do is to select an employee from the Employee list.
Each employee has the work shift you setup in Employee Master. Timesheet Calculator Template allows you to see each employee's work shift and working hours in the top right of the table.
The next step is setting the starting of the pay period and the weekend days. Here you'll use the lists setup in Pay Period Master and Weekend Master.
As this timesheet calculator is prepopulated with time entries (no need to add time-in out for each day), for each employee to use effectively, follow these simple steps for a correct configuration and maximum benefits via automation.Â
1. Change the employee's names with the ones of your coworkers (Cells A3-10).
2. Review work shifts if the default start day and end day timings are correct (ie shift A 9:00-17:30 is the default – 8 hours with a 30-minute break – Cells L1 -M1), but you have the option to change the shift hours in-out as you want (by default shift B 10:00-18:00, shift C 9:30-13:00, shift D 14:00-18:00).
3. Asign the right work shift for each employee (or change it accordingly – Cell B3-10).
4. Review the pay periods and the other general settings in the master (see below dedicated section) and update accordingly (E3-69).
5. Select the right employee (Cell D1).
6. Select the pay period (Cell D2).
7. Select the correct weekend days (Cell D3).
8. Update the hourly rate (Cell G14), overtime cost per hour (Cell H14), etc.
9. Your weekly hours should be visible and pre-populated automatically. You can now add Overtime, Sick, Vacation, Holiday, and Other type of time-off on the right date and for the right category (i.e., 2:00 means 2 hours).
10. The sheet automatically will calculate Total hours (Cell M13) and Total Pay based (Cell M15) on your work schedule, working hours, overtime, and hourly rate.
After each step, the template will be updated. This way, all your previous master settings will be in use.
Each day of work has several columns. They are:
For each day, you have only a few lines to complete: Time Break, Overtime, Sick, Vacation, Holiday, and Other. Timesheet Calculator Template computes the total amount of hours an employee worked in a day and in a week. It also provides weekly subtotals, like the total of vacation days or the total of overtime.
To have a complete paycheck, you need to enter a fee for each category. Consider regular hours, overtime, sick, vacation, holiday, and other factors. The template will compute the fees generated by each category and the total.
2
Bi-weekly Time Sheet works similarly with the Weekly Time Sheet. The difference is that this time you manage two weeks of work in a single table.
As with the Weekly Time Sheet, you have to follow the next steps:
3
Monthly Time Sheet starts with the date you provide as a starting pay period date. The table has all the days between the starting day and the ending of the month. For example, if you set Pay Period Starting to 1st of January 2024, the table will display the days between 26th of November and 30th of November. Monthly Time Sheet is designed for those who need to take into account the calendar month. Of course, if you set Pay Period Starting to the first day of the month, you'll have the entire month in the table.
To work with Monthly Time Sheet you have to follow the same steps as with Weekly Time Sheet. As always, setup the masters first to have all the data you need.
Timesheet Calculator Template is an Excel file with four sheets: Weekly Time Sheet, Bi-Weekly Time Sheet, Monthly Time Sheet, and Masters. The first three define the most popular pay periods. The fourth one gives you the tools to automatize the entire process.
Note: If you are new to Excel tables, we don't recommend you to work with the masters.
Masters
From Masters sheet, you can setup the template. It contains the general information which will help you automatize the employees' timesheets. It is very important to personalize Master sheet before starting to work with this tool. It has to reflect your business' needs. It contains the following Excel tables:
Work Shift Master
In the Work Shift Master, there are all the possible work shifts for your employees. They need to have a unique identifier. In the example above, they are A, B, C, D, but you can use any other text identifier like Morning and Evening. Shifts also have a start time and an end time you can configure as you like. Enter the Start time and End time of the shift in the Begin and End columns in HH: MM format. You can set as many work shifts as you like.
Employee Master
In Employee Master you have to list down the employees and their respective work shifts. Work shifts entered here must match the work shifts defined in the Work Shift Master. Otherwise Weekly, Bi-Weekly and Monthly Sheets won't show correct data. Timesheet Calculator Template comes with some examples of employee names, but you can enter as many as you want.
Weekend Master
The weekend is a two days period of not working days. Still, not all the countries share the same weekend days. Timesheet Calculator Template gives you the possibility to choose the weekends. Weekends are important because you can have special fees for your employees. You can also have different employees with different weekend days. Weekend master gives you the possibility to add as many weekend setups as you need.
Pay Period Master
Pay Period refers to the date when your employee begins to work. This list can include the starting day of every working week, two weeks period, and month. It is a way of making sure you don't miss any important date. Do the setup before you start working with employees sheets. You'll save time and eliminate errors. Introduce valid dates in the Pay Period Starting Date column.
Public Holiday Master
Public Holidays are also important when you manage working hours. They usually involved doing overtime, special fees, and special work shifts. Setup Public Holiday Master according to your business schedule. Introduce valid dates in the Date column. In employee's sheet, Public Holidays will be signalled with a red asterisk.
The Timesheet Calculator Template is a free tool. In order to use it, you need to install a software application able to open and edit an Excel file (.xlsx). Download Timesheet Calculator Template today and start tracking the time of your employees.