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

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. 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. 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. 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. • 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. ### 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 • 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 • Eventually, after completing the above step, the result should look like the screenshot below. ### 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. • 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. • 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. ## 💡 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. ## 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  