Get Access to World’s largest Template Library & Tools

How to Create a TimeSheet in Excel – Tutorial

Excel is one of the most popular tools used in small business used for keeping record of routine and periodic business activities. Among numerous other functions, Excel provides easy-to-use timesheet templates function to track of timings of employees working hours.

In this article we will set up a system for employees’ tracking time. But before we proceed, there are a few things we should consider. There is no single sheet that can accommodate workhours for all the employees for the reason that this factor is unique to every employee according to his shift and position. However, there are certain features and basic datathat are common to most timesheet models. Planning of timesheet starts with collection this basic information, which includes the reason of tracking time, for whom will this sample timesheet be used, and how will this time-tracking be used.

For time tracking, we obviously require time values but we cannot work with these values. MS Excel spreadsheet formulae make it easy to convert these values into workable integers which can be used easily in various calculations, workhours in this case. Generally, an “IF” function formula can convert values and differentiate between regular and overtime hours. With this basic calculation of regular and over time, we can then easily calculate employee’s pay or regular and over time working hours. Now, let’s get started on preparing timesheet on Excel.

> The Column Headers

First of all we will fill in headers. Headers will show what is our basic need, i.e. managing working hours of the employees. For this, type “Time In” in cell B1, “Time Out” in cell C1, “Time In” again in cell D1, and “Time Out” again in cell E1. Type “Regular” and “OT” in cellsF1 and G1 respectively. (OT = Over Time)

the column headers

> Week Days

Next we will mark the day of a week for which working hours are to be calculated. Type “Mon” (for Monday) in cell A2. Click the Fill Handle in the lower-right corner of cell A2 and drag it down to A8. All the week days will now under “Mon” appear as shown below.

week days

> The Text Labels forthe Weekly Totals

Now we will enter the core purpose of our timesheet, payment made to be made to the employees. For this type “Total Hours”, “Hourly Rate”, and “Total Pay” in cellsE9, E10and E11 respectively.

the text labels forthe weekly totals

> The Formula for Calculating Regular Hours

We can automate our work by adding some formulae for calculating the Working hours. Enter in cell F2the formula =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24). This formula calculates working time from cell B through E and converts the time value to integers.

the formula for calculating regular hours

> The Formula for Calculating Overtime Hours

Repeat the same procedure for calculating Overtime Hours. Use this formula in cell G2=IF(((C2-B2)+(E2-D2))*24>8,((C2-B2)+(E2-D2))*24-8,0). This will calculate working hours from column B through Column E and convert these values to integers.

the formula for calculating overtime hours

> The Formulas for Working Hours and Pay

the formulas forworking hours and pay

PS: All the necessary formulaeare now entered into the Excel timesheet.

> Format the Numerical Values

It is important to choose and mark your units to be used in the timesheet. Select all the cells spared for entering timings; in this case B2 through E8.Right-click and select Format cells from the menu and click the Numbers tab in the pop-up window. From the Category menu on the left,select Time;and select “1:30PM” from the Type menu pic your style of showing time. Click OK.

Next, select cells marked for pays; in this case F11, G11 and H11. Again, open the formatting window and select symbol $ present in Currency category in the Number tab.

Note:Alternatively, these formatting steps can also be carried through a shorter step. Select the cells that need to be formatted. Then change the formatting from Numbers group in the Home tab in the ribbon. Here you can change the Type, Currency and decimal places.

format the numerical values

> Adding Shades and Borders

Now we will give some colors to our timesheet. This will make it more professional and likeable to be used frequently. This is also a way to focus on the more important items or cells in the worksheet.

adding shades and borders

> Testing For Accuracy

testing for accuracy

> Save the WorkBook

After the timesheet is completed and test for correct formulae, the cell entries can be cleared. Keep everything else in the timesheet; including headers, weekdays and most importantly the formulae. Save the file with a name that is easy to remember and recognize.

More in Tutorials

How to Create a Professional Resume Using Microsoft Word - TutorialHow to Write a Cover Letter - Tutorial
How to Write a Professional Resignation Letter - TutorialHow to Make/Create a Thank You Letter for a Scholarship [Templates + Examples] 2023
How to Create a Lesson Plan in Word - TutorialHow To Make/Create a Business Letter [Templates + Examples] 2023
How to Create an Affirmative Action Plan - TutorialHow to Create a Gantt Chart in Excel Easily - Tutorial
How to Create a Resume on a Mac - TutorialHow to Create an Action Plan - Tutorial
How to Create a Lesson Plan for Elementary School - TutorialHow to Create a Family Tree Chart in Excel, Word, Numbers, Pages, PDF - Tutorial
How to Create a Pie Chart in Microsoft Excel - TutorialHow To Make/Create a Receipt [Templates + Examples] 2023
How to Create a Project Schedule in Excel - TutorialHow to Make a Certificate in Microsoft Word - Tutorial
How to Create a Calendar in Word - TutorialHow to Create a Birth Certificate using Word - Tutorial
How to Create a Calendar in Excel - TutorialHow to Make a Gift Certificate on Microsoft Word - Tutorial
How to Create a Family Tree in Microsoft Word - TutorialHow to Make/Create a Timeline in Microsoft Word [Templates + Examples] 2023
How to Create a TimeSheet in Excel - Tutorial5+ How to Make an Inventory Using Excel - Tutorial
How to Create an Invoice on ExcelHow to Create Receipts in Excel
How to Create a Family Tree in PowerPoint - TutorialHow to Use Bootstrap Tooltip
How to Use Glyphicons in Bootstrap 3How to Create Custom PHP Contact Forms
How to Use PHPBrew & VirtPHP - TutorialUnderstanding Bootstrap Grid - Column Wrapping
How to Create a Basic Timeline Template in Excel 2023 - Tutorial10 Top YouTube Channels for Game Development Tutorials in 2023!
What is a CSS Preprocessor and Why You Should Use IT?25+ Great Examples of InDesign In Use - Tutorials
Top Ui Photoshop Tutorials for Freshers5 Tips to Make Your Website Invincible From Hackers!
20 Easy Illustrator Tutorials For Beginners!19+ Illustrator Tutorials For Creating Isometric Illustrations!
20+ Useful Responsive Web Design TutorialsHow to Make an App - Free Online Tutorials
Html5 Tutorial for Beginners - Techniques and Examples
bottom banner