Excel Timesheet Template

Free Timesheet Template in Excel

Tracking daily time for an employee is a task that almost every company does on 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 from 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:

1. Free excel built-in timesheet templates that are there as readymade for you to download and directly use.
2. We also can create an excel timesheet of our own through absolute scratch.

We will walk you through these both ways one by one in detail.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Timesheet Template (wallstreetmojo.com)

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

#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 CenterMerge And CenterThe merge and center button is used to merge two or more different cells. When data is inserted into any merged cells, it is in the center position, hence the name merge and center. read more (for cells D8:E8 and F8:G8, respectively).

Step 3: In cell B9, use =IF(C3=””,””,C3) as a formula. This basic excel formulasBasic Excel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more capture 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 1^st half + Out time – In Time for 2^nd 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 cells H14 to L14, use the sum functionSum FunctionThe SUM function in excel adds the numerical values in a range of cells. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values can be numbers, cell references or ranges.read more to get 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 –

• Timesheet Calculator in Excel
• Top Accounting Excel Templates
• Excel Weekly Planner Template
• Payslip Template in Excel
• Calculate IRR in Excel