WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Timesheet Template

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

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

Excel New Option

 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.

Free Excel Timesheet Template

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Free Weekly Timesheet

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.

Click on Create Button

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

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 Center (for cells D8:E8 and F8:G8, respectively).

Step 3: In cell B9, use =IF(C3=””,””,C3) as a formula. This basic excel formulas 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 function 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.

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Timesheet Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More