How to Create a Weekly Timesheet in Excel (with Easy Steps)

Are you an HR professional, looking to make a weekly timesheet in Excel? Then, I have some good news for you! Because the following article demonstrates a step-by-step guide on how to create a weekly timesheet in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


What Is Weekly Timesheet?

Simply put, a weekly timesheet is a resource for figuring out how much time an employee spends working. This data can be used to compute pay and estimate the amount of time staff members devote to particular jobs.


5 Steps to Create a Weekly Timesheet in Excel

Generally speaking, weekly timesheets are used by the human resources department of an organization to keep tabs on how much time an employee spends working during the week. So, without further delay, let’s see the process in detail.

how to create a weekly timesheet in excel

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Step-01: Creating Basic Outline

Now, we have divided the process of how to create a weekly timesheet in Excel into steps. First, we need to prepare a simple outline of the timesheet. Hence, just follow these steps.

  • Firstly, type in a title for the data. Here, it is Weekly Timesheet.
  • Next, enter the Company Name (Stella Industries), Address, Location, and Contact Number respectively.
  • Lastly, enter the Names, Date, and Regular & Overtime Rates in USD.

After entering all the relevant information, your result should look like the image given below.

Creating Basic Outline of Weekly Timesheet in Excel

Read More: Excel Timesheet Formula with Lunch Break and Overtime


Step-02: Entering Necessary Data

After completing the above step, we’ll enter the weekdays and the start & end times. So, just follow along.

  • Initially, move to the B10 cell >> enter the weekdays in the Day column.
  • In turn, enter the time when employees start and end their work in the Starting Time and the Ending Time columns.
  • Finally, enter the time in the Lunch Start and Lunch End columns to complete the second step.

Entering Necessary Data into Weekly Timesheet

Read More: How to Create an Employee Timesheet in Excel (with Easy Steps)


Step-03: Calculating Total Work Hours

In the third step, we’ll compute the Total Work Hours for each day of the week. So, let’s see it in action.

  • To begin with, go to the G11 cell >> enter the expression given below.

=((D11-C11)-(F11-E11))*24

Here, the C11 and D11 cells represent the Starting Time and Ending Time while the E11 and F11 cells refer to the Lunch Start and Lunch End time respectively. Moreover, the multiplication by 24 converts the time to hours.

Calculating Total Work Hours

  • Following this, use the Fill Handle Tool to copy the formula into the cells below.

Eventually, your output should look like the picture shown below.

Using Fill Handle


Step-04: Computing Regular and Overtime Hours

In the fourth step, we’ll calculate the Regular Time and the Overtime hours using the popular IF function. It’s simple & easy, just follow along.

  • At the very beginning, navigate to the H11 cell >> type in the formula given below.

=IF(G11>=8,8,G11)

In this expression, the G11 cell refers to the Total Work Hour.

📃 Note: Here, we’ve considered 8 hrs as the daily working hour.

Formula Breakdown:

  • IF(G11>=8,8,G11)checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, G11>=8 is the logical_test argument that compares the value in the G11 cell with 8. If this value is greater than or equal to 8 then the function returns 8 (value_if_true argument) otherwise it returns the value of the cell G11 (value_if_false argument).
    • Output → 8

Computing Regular and Overtime Hours

  • Now, jump to the I11 cell >> and enter the following expression.

=IF(G11<=8,0,G11-H11)

Here, the G11 and H11 cells point to the Total Work Hour and Regular Time respectively.

Formula Breakdown:

  • IF(G11<=8,0,G11-H11) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, G11<=8 is the logical_test argument that compares the value in the G11 cell with 8. If this value is less than or equal to 8 then the function returns 0 (value_if_true argument) otherwise it returns the value G11-H11 (value_if_false argument).
    • Output → 0.5

Using IF Function

  • Eventually, after completing the above step, the result should look like the screenshot below.

Computing Regular and Overtime Hours


Step-05: Obtaining Total Payment

In the last step, we’ll compute the Total Payment using Excel’s SUM function. So, let’s begin.

  • In the first place, navigate to the H16 cell and type in the SUM function.

=SUM(H11:H15)

Here, the H11:H15 range of cells refers to the Regular Time.

Obtaining Total Payment in Weekly Timesheet in Excel

  • In a similar fashion, calculate the total Overtime in the I16 cell.

=SUM(I11:I15)

In this formula, the I11:I15 range of cells refers to Overtime.

Calculating Overtime Hours

  • Following this, compute the Total Payment by entering the expression below.

=H16*D8+I16*H8

In the above formula, H16, and D8 cells indicate the Weekly Hours (Regular Time) and the Hourly Rate of $25. Additionally, the I16 and H8 cells refer to the Weekly Hours (Overtime) and the Hourly Rate of $40.

Obtaining Total Payment in Weekly Timesheet in Excel


💡 Things to Remember

  • Firstly, you may get  #VALUE! error when subtracting two-time values if they are not in the proper format.
  • Secondly, you should change the time values to h:mm AM/PM format by pressing CTRL + 1 to open the Format Cells dialog box.

Using Format Cells


Conclusion

I hope this article helped you understand how to create a weekly timesheet in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

 

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo