A timesheet is a table where employees are recording the number of hours worked. In a more advanced version, a timesheet records payroll, tracks overtime and lunch hours, and records maternity leave and vacations.
On this page, you find everything you need to know to create and use an Excel timesheet. We’ve put together not only the technical Excel part but also the logic behind a timesheet. This comprehensive guide teaches you to track employee work hours, breaks, and vacations, to keep everything in order, and to calculate payrolls.
Note: To make things easier to follow, in the examples below we highlight in pale red the sections described in the text.
Open Microsoft Excel and start a new sheet
Just open the spreadsheet app from Microsoft on your Mac or PC and then open a new blank sheet.
Define working relationships
Before creating an Excel timesheet you have to decide what information you need. In simplest timesheets, there are lists with employees names, departments, etc. You can also define a list of time periods for which you create the timesheet (weeks, days, months, etc.), working days in a week, and national holidays.
Build your lists using columns and rows
Create a new sheet called ‘Lists’ and insert the items of each category in a different column. Then select all the items of each category and enter the category name in the space above column A. You’ll use these groups later to define the lists in other sheets of your timesheet.
In your main sheet, select the cell where you want to insert the list. Choose Data Validation from the Data menu and open Data Validation dialogue. In Settings tab choose List from Allow category and enter “=” and the group name at Source.
Click OK and the list is ready.
Time in Excel — Date & working hours details
Add columns for date and working hours. You can use the main sheet to calculate working hours for a week, a month, or a specific period of time. Format the date cell using a specific time format: select the cell, right-click and choose Format cells, choose Date for Category and a time type.
Time in Excel — Calculate weekly hours
At the end of the working period, you’ll need the formula to calculate the total number of working hours. Select a cell and use the function SUM() to compute the total.
Time in Excel — Calculate daily working hours
If you want to use time instead of the number of working hours, add columns for Arrival time, Departure time, and Working hours. Then format the cells for time: select the cells, right-click and choose Format cells, choose Time for Category and a time type. For cells from Working hours' column choose Custom for Category and h:mm for time type. Then use the formula Departing time — Arrival time to compute the exact number of working hours.
The total number of working hours will be computed using SUM() function and formatting the cell with the Custom type [h]:mm:ss.
Save your timesheet
Click File, click Save As, enter the desired name — i.e. Your-Company-Weekly-Template.xlsx select the Company HR folder, and then click Save.
Fill out the timesheet and review the results
Every timesheet will calculate the total number of working hours that your employees have entered. Nevertheless, things aren’t that simple. The timesheet has to include lunch breaks, weekends, overtime, and payment. Add columns for each of these and make sure your staff completes them every day.
Download now the timesheet template, and compare it with the one that you have created.
Incorporating a lunch break in your Excel timesheet is useful when you don’t have regular hours for lunch break. Some employees may take a longer break in one day and skip lunch break in the next day. In a weekly timesheet, the total amount or worked hours is the one that matters.
You have two methods to incorporate lunch break in an Excel timesheet. The first one works with time cells, while the second one works with hours.
If you choose the first method, add two new columns to your timesheet and name them “Went to lunch” and “Came back from lunch”. Then format the cells for time: select the cells, right-click and choose Format cells, choose Time for Category and a time type.
To compute the exact amount of daily working hours you have to subtract the lunch break duration from the time spent at work. Use the formula (Departure time - Arrival time) - (Came back from lunch - Went to lunch).
If you choose the second method, add only a column and name it Lunch break hours. Then format the cells for hours: select the cells, right-click and choose Format cells, choose Custom for Category and [h]:mm:ss for time type.
To compute the exact amount of daily working hours you have to subtract the lunch break duration from the time spent at work. Use the formula (Departure time - Arrival time) - Lunch break hours.
Don’t forget to save all the modification you’ve made!
Download now our timesheet template, to see how we have added the lunch breaks, and calculated the subtracted time.
A timesheet is useful not just for tracking worked hours but also for calculating payments. If the employees are paid by the hour, you need to add a cell called “Hourly pay ($/hour)” and a cell called “Weekly payment” to each timesheet. Then add the value for the hourly pay (7 in our example).
To compute the weekly payment use the formula ( Weekly total * 24* Hourly pay ($/hour) ). The formula will convert the time in cell Weekly total in hours and will multiply the result with the value in cell Hourly pay ($/hour).
Things are simpler if you have several payment categories and you define a list for them in Lists sheet (for example, you can have Management - $20/hour, High education - $10, and Internship - $7). In this case, you won’t have to enter Hourly pay ($/hour) for each employee. You select the corresponding value from a list.
Because overtime is usually paid differently you need to pay attention to the total worked hours. For example, if an employee’s norm is 40 hours/week and Weekly total is 57 hours, the difference has to be paid at a higher fee.
Add cells for Weekly norm, Overtime hours, and Overtime pay a $/hour and add corresponding values (in our example, Weekly norm is 40 hours and Overtime pay is $10/hour). As Weekly total hours, the cells Weekly norm and Overtime hours have to have Custom [h]:mm:ss format.
To compute the weekly payment use the formula Weekly norm*24*Hourly pay ($/hour)+Overtime hours*24*Overtime pay ($/hour).
Download now our timesheet template. You will have all the overtime calculations already provided.
Using cell formats may be confusing when it comes to working with time and dates. Excel automatically transforms a cell in a time cell if you enter a value similar to “7:00 AM”. But if you change the format from Time to General you’ll have the value 0.291666666666667 instead. That’s because for Excel an hour is the 24th part of a day and has the numerical value of 1/24.
Also, pay attention to have the same time format for all your time cells. Working with AM (Ante Meridiem, before noon) and PM (Post Meridiem, afternoon) or with the 24-hours format has to be constant in your timesheet.
The formula for calculating hours
Calculating worked hours is a matter of adding and subtracting values in time cells. The result has to be a number of hours and minutes. This means you have to format the result cell to the Custom h:mm format.
Note: In the Format Cells dialog you can preview the effect of each format change in the Sample field.
If your timesheet includes non-working days, the employee shouldn’t enter fictional values just to have the timesheet working. One should be allowed to use a special term, like “OFF”, for the non-working days. For example, instead of entering the arrival time, the employee will enter “OFF” and will leave the rest of the cells empty.
The formula for calculating worked hours has to include non-working days. This implies working with IF function: IF(logical_test, value_if_true, value_if_false). Use the following formula: IF (Arrival time =”OFF”, 0, (Departure time - Arrival time) - (Came back from lunch - Went to lunch))The formula for calculating dates
The cell format Time h:mm allows us to use only an amount of hours smaller than 24. But what can we do when an employee starts working today and ends working tomorrow? A night shift will be a good example of this.
Let’s assume an employee starts working on June 11th, at 8 AM and ends work on June 12th, at 10 AM. If we use the Time format, the amount of worked hours will be 50 minutes. To have the correct amount of worked hours (24 hours and 50 minutes), we need to use the Custom [h]:mm format.Converting time in Excel
Working with time format cells is great for computing the number of hours an employee worked in a given period. But it isn’t useful when you want to calculate how much money you have to pay for an employee. When you pay your employees at a specific rate per hour you need to convert time into a decimal number.
Due to the fact that Excel considers an hour the 24th part of a day, all you have to do to convert time into a decimal number is to multiply it by 24 and change the cell format to Number with two decimal places. If you want to keep working with time format cells, convert time inside the formula for computing Weekly payment: Weekly norm*24*Hourly pay ($/hour)+Overtime hours*24*Overtime pay ($/hour).
If you want to use minutes as a reference, simply multiply time value to 1440 and change the cell format to Number with no decimal places. This means you first converted time into a decimal number by multiplying it with 24 and then you converted hours in minutes by multiplying with 60. 24*60 = 1440.
Download now our timesheet template. This might be helpful especially when it comes to converting time.
Using a timesheet template saves you time and improves your work accuracy. You'll have all the information in one place, simple tools to calculate individual values and totals, and straightforward evidence. Among other benefits there are:
If you want to use a timesheet template but don't have time to create your own, use our free timesheet templates. They are available as Doc, Spreadsheet, and PDF. You can download them right now. No subscription or email required.
These free timesheet templates help you track time within your company. You can modify them according to your requirements, print them, and distribute them to your team.