Here is a simple process to follow to make a timesheet in Excel in a few moments to facilitate the tracking of working hours.
Step 1: Enter Titles And Dates
Start by naming each of the columns you will need. Write the following titles in the first cell of each of the columns in the first row: Date, Start of time slot, End of time slot, Unpaid break time, Total hours.
In the first column, under the heading “Date”, begin the dates that correspond to each day of the pay week to be processed. Then write the title “Total” at the bottom of this column.
Step 2: Customize The Format Of The Cells
Before automating the work hour calculations, be sure to customize the format of cells that contain hours. To do this, select all cells with hours, click “Custom” on the main menu at the top, then click “Hours.”
Step 3: Automate The Calculations
In order to automate the calculations of the working hours of your employees, enter the following calculation formula under the heading “Total hours”: = (C2-B2-D2).
Then click in the lower right corner of the cell, hold down the left mouse button, and drag the cursor to the bottom cells to apply the formula to the other days of the week.
To calculate the total number of working hours for the week, enter the following calculation formula in the cell following the last day of the week: = SUM (E2: E8), or = SUM (E2: E8) in the English version of Excel.
Step 4: Customize The Look Of Your Timesheet
To make your timesheet more complete, personalize it by adding the information needed for payroll processing such as employee name, employee number, pay period date, etc. You can also change the appearance of the sheet to make it easier to read.
You can then duplicate your template to use it for each of your employees.
Make sure your Excel timesheet template is complete and easy to read, but don’t spend hours customizing it. Keep it very simple.
If you use work planning software, be aware that most of them offer tools dedicated to the management of timesheets which greatly facilitate the task of managers.
