Free Timesheet Template in Excel
Tracking daily time for an employee is a task that almost every company does for a daily basis and maybe check it weekly, biweekly, monthly. Excel comes out handy in this case due to the versatility it possesses. We can create a free timesheet template under Excel to capture the daily/weekly timings of employees. Due to the variety of customizations available, we can’t say there is one standard excel template to track the time. It varies organization to organization and format may also change. In this article, we will walk you through the excel timesheet templates, both of which are built-in as well as can be created simply through scratch.
We have two ways to create the excel timesheet template:
- Free excel built-in timesheet templates that are there as readymade for you to download and directly use.
- We also can create a excel timesheet of our own through absolute scratch.
We will walk you through these both ways one by one in detail.
How to Create Timesheet Template in Excel?
Below are the examples to create a timesheet template in excel.
#1 – Free Built-in Timesheet Template in Excel
Follow the steps below to be able to use the free built-in timesheet template in Excel.
Step 1: Open a new Excel. Navigate to File menu > click on New. It will allow you to open a new template.
Step 2: Once you click New, you will see a list of thousands of online templates which are under excel and can be used as well as downloaded by the user. Put “Timesheet” under the search box that appears on the same sheet and then hit Enter to load all the timesheet templates.
You need to be connected to a healthy network so that you can download such templates.
Step 3: Out of all the excel templates, click on the Weekly Timesheet template.
As soon as you click on it, a new dialogue box will open with a template description and Create button. Click on Create button so that the file can be downloaded and used in your system.
You can see a partial screenshot of worksheet after it gets downloaded as below:
This is one way using which you can create a timesheet template in excel.
#2 – Creating Excel Timesheet from Scratch
Now, we are going to create an excel timesheet template from scratch. Follow the steps to create the same.
Step 1: We need to add fields for the informative details first. Such as Employee Name, Start Date, Department, Manager, etc. Populate these fields across the cells B2:C5. Format these cells with Font/Font Size – Calibri/12, Bold, Border Type – All Borders.
Step 2: Add the column labels for Date, Day, In Time, Out Time for First Half and Second Half, Hours Completed, Overtime, Sick Leave, Personal Leave across the cells B8 to K9. Also, keep 5 lines as blank so that you can add the data for 5 days (We are assuming the week is for 5 days and 2 days are off – Saturday and Sunday respectively).
The data should be formatted as Font/Font Size – Calibri/12, Wrap Text, Merge and Center (for cells D8:E8 and F8:G8 respectively).
Step 3: In cell B9, use =IF(C3=””,””,C3) as a formula. This excel formula captures the date in cell C3. You only will change the date to C3.
Step 4: In cell B10, use formula =IF(B9=””,””,B9+1). This formula captures the date in cell B9 and makes an increment of 1 in it to get the next date. You can drag this formula across all the cells until B13 to get the dates. Change the Date format as Custom dd-mmm-yyyy.
Step 5: Now, in column C across C9:C13, we need to extract the day for different dates in column B. Use, =TEXT(B9,”dddd”) in cell C9 to get the day value as “Monday” and drag the formula until C13.
Step 6: Now, we need to capture the hours completed (Column H). Use, =IF(((E9-D9)+(G9-F9))*24 > 8, 8, ((E9-D9)+(G9-F9))*24) as a formula to capture the hours completed under column H.
This formula checks if total working hours (Out Time – In Time for 1st half + Out time – In Time for 2nd half) are greater than 8 or not (24 is used to make the data convenient for 24 hours’ time format). If total working hours are greater than 8, it will consider it as 8, else will consider it as the total working hours captured by the formula. Drag the formula across the cells.
Step 7: Now, using the same logic, we are going to calculate the overtime value. Use =IF(((E9-D9)+(G9-F9))*24>8,((E9-D9)+(G9-F9))*24-8,0) as a formula under cell I9.
This formula finds if the total working hours are greater than 8 for the day, then the value after subtracting 8 hours from total working hours will result under cell I9 as Overtime, else it will be zero.
Step 8: We will set the default values as 00:00 for Sick Leave and Personal Leave columns (Column J and K respectively). The values of these columns can’t be formulated as we do for hours completed and overtime.
Step 9: Under Column L, add a new title as “Total Daily Hours”. This column will be the one that can give you all the daily completed hours. You can use, =SUM(H9:K9) as a formula under L9 to capture the daily total hours for 18-Nov-2019. Drag the formula across the cells as well.
Step 10: Across cell, H14 to L14, use the sum function to sum up the total hours for the week associated with each column.
I also have added, the In Time, Out Time in the sheet and could see the working hours reflecting in it.
Things to Remember:
- Keep an eye on the cell format. We have used the 24 hours format for timings in each column.
- This timesheet template is a weekly planner and can be modified as a Biweekly or Monthly timing as well.
- Also, note that the sheet is protected and hence the user may not be able to change the formatting for this.
Recommended Articles
This has been a guide to Excel Timesheet Template. Here we discuss how to create a free weekly timesheet template under Excel to capture the daily timings of Employee. You can learn more from the following articles –
- Top Accounting Excel Templates
- Excel Weekly Planner Template
- Payslip Template in Excel
- Group Data in Excel
- Timesheet Excel Calculator
- Dashboard Training in Excel
- Personal Budget Excel Template
- Blank Invoice Template in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
