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.

Excel-Timesheet-Template

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.

You can download this Timesheet Excel Template here – Timesheet Excel Template

#1 – Free Built-in Timesheet Template in Excel

Follow the below steps to use the free built-in timesheet template in Excel.

  1. Open a new Excel. Navigate to File menu u003e click on New. It will allow you to open a new template.

    .

    Excel New Option

  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 boxSearch BoxA search box in Excel finds the needed data by typing into it, then filters the data and displays only that much info. When working with large datasheets, this simple tool may save a lot of time.read more that appears on the same sheet and then hit Enter to load all the timesheet templates.


    Excel Timesheet Serach

    You need to be connected to a healthy network so that you can download such templates.

  3. Out of all the excel templates, click on the Weekly Timesheet template.

    Select Weekly Timesheet

  4. As soon as you click on it, a new dialogue box will open with a template description and Create button. Click on the Create button so that the file can be downloaded and used in your system.

    Weekly Timesheet (Click on Create Button)

  5. You can see a partial screenshot of the worksheet after it gets downloaded as below:

    Weekly Timesheet (Template Preview)
    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.

Timesheet Template - Step 1

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).

Timesheet Template - Step 2

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.

Timesheet Template - Step 3

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.

Timesheet Template - Step 4

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.

Timesheet Template - Step 5

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.

Timesheet Template - Step 6

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.

Timesheet Template - Step 7

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 8

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 9 (Total Daily Hours)

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.

Step 10 (Sum Function)

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.

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *