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

Get FREE Advanced Excel Exercises with Solutions!

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


Watch Video – Create a Weekly Timesheet Template in Excel


how to create weekly timesheet in excel


What Is Weekly Timesheet?

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.

Creating a weekly timesheet in Excel is particularly helpful because of the ability to customize it easily. Moreover, spreadsheets are repeatable for every employee. A dynamic timesheet can automatically calculate times and salaries of them. We can easily achieve that through a variety of functions and formulas Excel offers.


How to Create a Weekly Timesheet in Excel: Step-by-Step Procedure

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.


Step 1: Create Necessary Particulars

First of all, we need some particulars of the employees. Such as the employee’s name, ID, the project he is working on, contact information, contract salary, etc. We will place them at the top of the weekly timesheet we are going to create in the Excel spreadsheet.

  • We are going to merge the range B2:L2 and insert the title there.

heading of weekly timesheet

  • To put in the particulars, we have merged some cells below them for headers and information. This is more so to make the timesheet more presentable.

employee particulars

You can be flexible here with the information available in B4:B9. However, particulars from K5:K8 are a must and will be used later on for the calculations.


Step 2: Make Basic Outline of Timesheet

Now that we have the particulars set for the timesheet, we are going to prepare some cells below to insert headers.

  • In our spreadsheet, we have prepared the range B12:L12 for the headers. We have used the Wrap Text feature for these cells.

heading of weekly timesheet

This feature allows the row height to automatically adjust according to the text length in the cells.


Step 3: Insert Dynamic Days and Dates

Note that, we have selected a date in cell K5. This indicates the starting date of the weekly timesheet we are going to create in Excel. We need to use the TEXT function so that the days appear on the timesheet dynamically.

To make the days and dates of the timesheet automatically fill up based on this value, follow these steps.

  • Select cell B12 and insert the following formula.
=K5

Note: Make sure to keep the cell in Date format.

adding dates dynamically

  • In cell B13 insert the following formula.
=B12+1

dynamic date formula for rest of the cells

  • Now drag the Fill Handle down to cell B18.

filling with date formula

  • For days, select cell C12 and insert the following formula.
=TEXT(B12,"dddd")

days from date formula

  • Now fill the formula down to cell C18.

filling with days formula


Step 4: Calculate Absence Hours

The starting and end time as well as break time inputs are to be put manually in the timesheet. They can vary depending on the day so we can’t do much with that.

We need to prepare the “Absence Hour” column in such a way that it calculates the break time automatically. For ease of calculation, let’s first fill up some standard values for the manual inputs.

sample timesheet entries for calculation

Now to calculate the “Absence Hour” insert the following formula in cell H12 and replicate it till H18.

=G12-F12

filling with absence hour formula


Step 5: Calculate Regular Work Period and Overtime Period

We need to calculate the regular period and the overtime period an employee is working next.

  • To find the hours he is working, select cell K12 and insert the following formula.
=(E12-D12-H12)*24

regular work period formula

  • Now copy it down to cell K18.

filling with regular work period formula

  • Do the same for the range L12:L18 but with the following formula.
=(J12-I12)*24

overtime formula


Step 6: Compute Weekly Hours

With all the inputs we are going to have in the weekly timesheet, we need the total hours an employee is putting in.

As rates can be different for regular and overtime hours, we have calculated them separately. We are going to calculate the total hours of each section separately too.

Excel offers the SUM function that can take up a range of values and return the total of them. That fits our purpose here.

  • To get the total regular work period, select cell K19 and insert the following formula.
=SUM(K12:K18)

total weekly hour for regular time

  • Similarly, select cell L19 and insert the following formula to get the total overtime period.
=SUM(L12:L18)

total weekly hour for overtime


Step 7: Calculate Weekly Pay

It is a common practice to have the weekly payment calculated in the same Excel spreadsheet we create a timesheet on. So let’s cover that too. You can skip it depending on whether or not you want to have them in your version.

  • For that, we have merged K20 and L20 and inserted the following formula.
=K19*K7+L19*K8

total payment calculation


Step 8: Apply Conditional Formatting for Weekends

Let’s apply some formatting now to mark the weekends in our timesheet. You may have noticed that all days of the week are here in the timesheet.

We have done so because some employees may be working overtime hours on weekends and we have to keep track of that too.

We will be applying conditional formatting here so that the formatting remains dynamic too. In our case, we are assuming Sunday will be the weekend.

  • Select the range B12:L18 and then select Conditional Formatting. You can find it in the Styles group of the Home tab.
  • From the drop-down menu, select New Rule.

applying new rule for conditional formatting timesheet

  • In the New Formatting Rule box, select Use a formula to determine which cells to format under Select a Rule Type.
  • Then write down the following formula in the formula field.
=$C12="Sunday"
  • Select a format style by clicking on the Format button.

conditional formatting formula

  • Once you click on OK, the row with “Sunday” in it will be formatted.

conditional formatting applied to timesheet

If you have more than one weekend, repeat the process for each day.


Step 9: Test Weekly Timesheet for New Entries

The timesheet is completed at this point. But let’s test it out for new entries and see if it is working dynamically or not.

  • First, let’s change the starting weekday to a new one in cell K6. The days and dates will change automatically now in the timesheet. The weekend format also shifts as we can see from the figure below.

dynamicity check of timesheet

  • Now let’s try inserting new values in the second and third rows.

timesheet check for new values

We can see the weekly hours and total payments changed automatically after the entries. Thus we can conclude our weekly timesheet is working dynamically.


Pros and Cons of Creating Weekly Timesheet in Excel

There are many benefits involved when we create a weekly timesheet in Excel-

  • If a timesheet created in Excel needs some customization, we can do that easily by editing the Excel cells according to the needs.
  • Different functions and formulas are available in Excel that make the calculations very easy and dynamic.
  • Excel offers some built-in data analysis tools that we can use on timesheets if we need them.
  • As the timesheets can be very dynamic, the tracking can be very efficient as well.
  • The Excel spreadsheets with the timesheet can be easily integrated and shared with other software, systems, and users.

However, creating a timesheet is not free from having downsides. Some may include:

  • Data entries always have to be manual. It can be integrated with other software to automate the process. But it is not always user-friendly or cost-friendly.
  • The manual data entries are also prone to data manipulation.
  • While some automation is possible, it still may not live up to the point of other timesheet software.
  • Timesheets of Excel lack some dedicated features like reminders, project management integrations, etc.

Things to Remember

  • Keep track of the formats while working with date values.
  • To count work periods, make sure to multiply the differences by 24 (for the hours of the day).
  • Repeat Conditional Formatting for each weekday.
  • you may get  #VALUE! error when subtracting two-time values if they are not in the proper format.

Frequently Asked Questions

  • How do I format cells to display time in the correct format (hours and minutes)?

To display a cell value in a certain format, select the cell and press Ctrl+1 (shortcut for formatting cell). Then in the Number tab, select Time to find the time format you want. Or you can select them from the Custom section too.

  • Can I track the time for multiple employees on the same Excel timesheet?

Yes, you can track the time for multiple employees on the same spreadsheet. However, there needs to be different charts side by side. Otherwise, it would be too chaotic and the timesheet won’t serve its purpose.

  • Is it possible to create a timesheet that accounts for different pay rates or shift differentials?

You can create a timesheet with different pay rates for different shifts. We have already included two different pay rates in our timesheet- regular and overtime. If you need any more than that, you can add extra columns to track those times and just copy what we have done with overtime pay with the regular pay. You just have to integrate the new one in the same way in the total pay in the end.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below. You can also use it as a template as the spreadsheet is dynamic and can adjust according to your needs.


Conclusion

That concludes our discussion on how to create a weekly timesheet in Excel. We have selected some cells for the particulars and a timesheet by day. We calculated the regular hours and overtime hours worked each day by the difference between the check-in and out time. Multiplying them with the hourly rates in the contract and adding them gave us the total pay in the end. This is how we created a simple interactive weekly timesheet. Hopefully, you can now create weekly timesheets on your own in Excel easily too.

I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.


<< Go Back to Timesheet | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo