Step 1: Enter Headings and Dates
Start by naming each column you need. Write the following headings in the first cell of each column in the first row: Date, Time In, Time Out, Unpaid Break Time, Total Hours.
In the first column, under “Date,” enter the dates corresponding to each day of the pay week in question. Then enter the title “Total” at the bottom of this column.
💡 To speed things up, click and hold the lower-right corner of a cell containing a date and drag the cursor to the desired cells to create a logical date range.
Step 2: Customize the Cell Format
Before automating work hour calculations, be sure to customize the format of cells containing hours. To do so, simply select all cells with hours, click on “Custom” in the main menu at the top, and then click “Hours.”
💡 To speed things up, click on the first cell to customize and drag the cursor to the cells you want to select.
Step 3: Automate Calculations
To automatically calculate your employees’ work hours, enter the following formula under the “Total Hours” heading: =(C2-B2-D2).
Click and hold the lower right-hand corner of the cell and drag the cursor to the cells below to apply the formula to other days of the week.
To calculate the total number of hours worked for the week, enter the following formula in the cell following the last day of the week: =SUM(E2:E8).
Step 4: Customize Your Timesheet’s Appearance
To make your timesheet more complete, customize it by adding the necessary information for payroll processing such as employee name, employee number, pay period date, etc. You can also change the sheet’s appearance to make it easier to read.
You will then be able to duplicate your template and use it for each of your employees.
Make sure your Excel timesheet template is complete and easy to understand, but don’t spend hours personalizing it. Keep it simple. Similarly, you can also create a work schedule in Excel in a few simple steps.
If you’re using an employee scheduling software, be aware that most of these solutions offer time and attendance tools that greatly facilitate the work of managers.
To save time and work more efficiently, you can also download our free Excel timesheet template.