How to make a timesheet in Excel
To make a timesheet in Excel, you have to envision first what information you’d like to know.
Some of the most useful information include:
- Date (most common: first/last day of the week and date range)
- Days and shifts (morning, afternoon, evening)
- Rate (usually per hour)
For this tutorial, we’ll make a weekly timesheet template for a regular employee working on a day shift. The employee will need to enter the time he went in and out for morning and afternoon (since there’s lunch in-between).
Note that what you’ll be getting by the end of this exercise is a simple Excel timesheet template with formulas.
Here’s how we imagine the (filled-out) finished timesheet to look like:
First off, let’s enter the labels:
The basic operation of our timesheet would need to compute how many hours the employee worked during the day (morning and afternoon).
To accomplish that, subtract the ‘Time out’ to the ‘Time in’ and add the difference for both morning and afternoon.
In words, that would be: (morning time out – time in) + (afternoon time out – time in).
In our sample, let’s add the first row of information so we could test out the formula above:
- 12:00 – 7:00 = 5 hours
- 4:00 – 1:00 = 3 hours
- 5 + 3 = 8 hours
Awesome, right? We can now apply the same formula to the rest of the rows.
For convenience, simply drag the handle on the bottom right of the cell downwards:
Now that it’s working, let’s add the ‘SUM’ function to compute the total hours worked during the week.
To do so, let’s use the function’s syntax: =SUM(number1, [number 2], …).
What we want the function to do is add all the total hours per day so we can have the total hours worked for the week.
Feels like smooth sailing? Unfortunately, this is the first part you’ll encounter a bit of a struggle.
The format of the cell which contains the total hours will successfully add all the hours of the week. But the problem arises when the sum reaches 24 hours. Once it does, it will reset to 1.
Why? Because the format is set to a custom ‘Time’ and we all know that a day only lasts 24 hours.
To illustrate, let’s add all the hours from Tuesday to Friday and see what happens:
The employee worked for 5 days straight and the total hours he got from the tracker is only 15 hours. That can’t be right!
To fix this, we have to change the format of the cell.
To do so, click the cell. Then, on the ‘Home’ (default) tab, click the dropdown bar of the number format on the ‘Number’ group.
Then, click the ‘More Number Formats…’ at the bottom of the dropdown options:
Then, go to the ‘Time’ category and change the format ‘Type’ to hh:mm:ss as shown below:
We’re now on the final stretch — calculating the total bill.
What we have to do is multiply the total hours worked for the week with the employee’s rate which is $19/hour (in our example).
Here’s the catch:
You can’t directly multiply the total hours by 19. The reason is, the format of the total hours is in ‘Time’.
If you do so, the total bill would just be $30.88.
Obviously, 39 multiplied by 19 isn’t 30.88.
The reason for that is ‘Time’ is still a numeric value. However, it’s stored differently.
The value 39:00:00 is converted to 1.63 or roughly a day and a half. Excel automatically converts 24 hours into 1.
To remedy this, multiply the ‘Time’ value first by 24 before multiplying it by 19. This is how you convert time to decimals in Excel.
As Excel follows the PEMDAS order of operation, place a bracket before and after the values:
That’s it! Worked like charm, right?
You can now use the timesheet and start passing it to your employees. 😊