How to create a timesheet calculator with Microsoft Excel?

If you want to level up from paper timesheets, one common solution is using the ever popular tool Microsoft Excel. Calculating timesheets in Excel isn't super difficult but there are definitely some edge cases that can make this an infuriating exercise. We've written a step by step tutorial as well as a video to help assist you in creating a timesheet calculator with Microsoft Excel.

Video Guide

Tutorial Guide

1. Open Microsoft Excel on your machine
2. Enter in the following fields in Excel and the corresponding timesheet data for the employee. You can enter times as 9 AM, 5 PM. etc. Just ensure to leave a space before AM/PM, otherwise Excel does not recognise it as a time.

3. Now we're going to add two additional columns "Break Duration" - which is calculating the break time to deduct from the daily hours worked and "Total Hours Worked" which is the time worked by the employee minus any breaks to be deducted.

4. Enter the formula for the Break Duration column =D4-C4 e.g (Break End time - Break Start Time = Break Duration)

5. We need to format this Break Duration Cell to a custom format that shows hours and minutes. This is required for correct calculation of your timesheet. To do this, right click on the cell and click "Format Cells". Then select "Custom" and enter in the format [h]:mm and then press Ok.

6. Enter the following formula for the Total Daily Hours column =E4-B4-F4 (End Time - Start Time - Break Duration)

7. As we did before we need to format the Total Daily Hours cell to be a custom format of [h]:mm. Right Click on the cell, choose Format Cells and enter in the custom format below.

8. Drag the formula for the Break Duration cell and Total Daily Hours cell to the cells below. You should see the following.

9. Finally let's add a Total Hours worked column with the formula =SUM(G4:G8)

10. Finally to ensure everything is consistent, set this new cell to a custom format of [h]:mm as well. See step 5 for more details.

And that's it! With a few tweaks, Microsoft Excel can be used as a simple calculator for your staff timesheets.

If this article helped you or you have any questions please do let us know at support@timekeeper.co.uk. Thanks for reading!

Microsoft Excel is a powerful tool that can be used for timesheets but is still a manual time consuming process. If you'd like to explore a more sophisticated time and attendance system for your business - why not check out TimeKeeper. Start your free trial today!