EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Timesheet in Excel
 

Timesheet in Excel

Karthikeyan Subburaman
Article byKarthikeyan Subburaman
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 21, 2023

Timesheet in Excel

 

 

Timesheet in Excel

A timesheet is a system for recording the employee’s time spent on each job. In Excel, we normally use a timesheet to calculate the employee’s timings like IN and OUT timings, how many hours an employee worked for a day, and the exact “BREAK” time he has taken. In Excel, this timesheet will summarize the number of hours worked by each employee; to calculate these timings, we can use the timesheet to elaborate on it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create Timesheet Template in Excel?

Creating a timesheet template in Excel is very simple and easy. Let’s understand how to create a timesheet template in Excel with some examples.

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

Excel Timesheet – Example #1

In this example, we are going to create a timesheet of employees by calculating how many hours an employee worked. First, consider the employee database below with IN and OUT Timings.

Example 1-1

The above employee database has a date, employee name, TIME IN, and TIME OUT. Now we need to find out how many hours an employee worked by following the below steps:

  • First, create a new column named Number of Hours Worked.
  • Make sure that the cell is in the proper time format.
  • To calculate an employee’s no of hours, we will calculate the formula as OUT TIMINGS – IN TIMINGS.

Timesheet in Excel Example 1-2

  • By default, Excel will return the result in decimal numbers, as shown below, where it’s not an exact number of hours.

Timesheet in Excel Example 1-3

  • We need to change this general format to time by formatting the cell, as shown below.

Timesheet in Excel Example 1-4

  • Once we click on the format cell, we will get the dialog box below, choose the exact time format, and click ok.

Timesheet in Excel Example 1-5

  • After applying the format, we will get the output result as follows.

Timesheet in Excel Example 1-6

  • If we have IN and OUT Timings, we can use the formula as shown above, but we have BREAK TIME IN and TIME OUT in this example. Hence we can use the simple Excel timesheet formula as =(E2-D2)+(G2-F2).

Timesheet in Excel Example 1-7

  • By applying this formula, we will know how many hours an employee worked for a day, and the output will be displayed as follows.

Timesheet in Excel Example 1-8

  • We just need to drag the cell H2 downwards, and the formula will be applied to all the cells.

Result of Example 1

In the above example, we can see the time difference in how many hours an employee has worked and the breakup timings given for each employee as time OUT.

Excel Timesheet – Example #2

In the previous example, we have seen how many hours an employee has worked using the normal arithmetic formula; now, in this example, we will use the time function in 24 hours format.

Suppose assume that management wants to pay the employees on an hourly basis based on their work timings.

As shown below, let’s consider the employee database with appropriate timings records.

Example 2-1

Here we can see the employee IN and OUT timings for various employees. Now we need to calculate the number of hours the employee has worked by following the below steps:

  • Insert a new column named Hours Worked.
  • Apply the normal Excel timesheet formula as =E2-D2, as shown below.

Timesheet in Excel Example 2-2

  • Now we can see that decimal values have appeared for Hours Worked.

Timesheet in Excel Example 2-3

  • This error normally occurs because the time is not in 24-hour format.
  • To apply the Excel timesheet formula by, multiplying by 24 as =(E2-D2)*24.

Timesheet in Excel Example 2-4

  • After applying the above formula, we will get the following output result.

Timesheet in Excel Example 2-5

  • Drag the cell F2 downwards, and the formula will be applied for all the cells, as shown below.

Timesheet in Excel Example 2-6

Now we will calculate how many employees are going to get paid on an hourly basis. Let’s assume an employee will be paid Rs.20/- per hour and follow the steps below.

  • Insert two new columns as Hourly Rate and Gross Pay as given below.

Timesheet in Excel Example 2-6

  • In the hourly Rate Column, enter Rs.20/-.

Timesheet in Excel Example 2-7

  • To apply the Excel timesheet formula as Gross Pay = Hourly Rate * Hours Worked.

Timesheet in Excel Example 2-8

  • We will get the below result as follows.

Timesheet in Excel Example 2-9

  • Drag the cell H2 downwards, and the formula will be applied for all the cells, as shown below.

Result of Example 2

Here we calculated the Gross Pay of an employee based on the Number of Hours Worked.

Either way, we can create a new column, Hourly Rate, as Rs.20/- in a fixed column and multiply it by the Number of Hours Worked. Let’s see with an example as follows.

Excel Timesheet – Example #3

Consider the same employee data record with TIME IN and TIME OUT records.

Example 3-1

We have created a new fixed column named Hourly Rate as Rs.30/-. So the employee will get paid Rs.30/- Per Hour.

Now Hourly Rate has a fixed column, so whenever the Rate changes, it will get populated and reflected in the Gross Pay column, as shown in the below steps.

  • Insert the Excel timesheet formula in Gross Pay Column as =$c$1*F5 shown below, i.e., Gross Pay = Hourly Rate * Hours Worked.

Calculation of Gross Pay 1

  • We can see that Gross Pay has been calculated on the Hourly Rate Basis.

Result of Gross Pay 1

  • Drag the cell G2 downwards, and the formula will be applied for all the cells, as shown below.

Drag the Formula

  • Next, we will calculate the Total Gross Pay by adding the employees’ Gross Pay.
  • Use the formula =SUM(G5:G19).

Timesheet in Excel Example 3-5

  • We will get the below output as follows.

Timesheet in Excel Example 3-6

Hence we have calculated Total Gross Pay as Rs.3900/- and Hourly Rate as Rs.30/- If the Hours Rate changes, the values will be automatically changed, and the same Gross Total will also be changed.

Let’s see what happens if the Hourly Rate is changed to Rs.10/- and we will get the below result.

Result of Example 3

Now we can see the difference in each employee that Gross Pay has been getting decreased because of Changes in Hourly Rate and at the same time we got Total Gross Pay as Rs.1300/- and Hourly Rate as Rs.10/.

Things to Remember

  • Maintain proper timing format while creating a timesheet for employees.
  • Ensure all cells are formatted in 24-hour format, or Excel will throw a decimal value.
  • Ensure that AM and PM are mentioned in the timings because if the OUT TIME is Greater than IN TIME, excel cannot calculate the number of hours worked.

Recommended Articles

This has been a guide to Timesheet in Excel. Here we discuss how to create a Timesheet Template in Excel, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more –

  1. Time Function in Excel
  2. Worksheets in Excel
  3. VBA Worksheets
  4. Timecard Template in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Timesheet Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Timesheet Excel Template

EDUCBA

डाउनलोड Timesheet Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW