Spreadsheet Tips - A simple Excel Timesheet

If you sell your time by the day or by the hour, you want a quick and easy-to-use timesheet which will calculate your billings accurately and automatically. All you need to do is input your rates and your hours. Follow the example below using your own data. Alternatively, the sample timesheet included in this example is available as a free download.

Using Excel's HOUR and MINUTE functions along with some arithmetic functions allows us to calculate weekly billing based on your rate and your hours.

Spreadsheet Data 1 shows the initial timesheet. For this example, I have put in an hourly rate of £20/hour. This means putting 20 in cell marked 'Rate (£)' and 1 in 'Per No of Hours'. This allows us to make calculations for daily rates as well as straight hourly rates. If your rate is based on an 8 hour day, you would put the daily rate against 'Rate (£)' and 8 against 'Per No of Hours'. I have filled in the hours worked using the 24-hour clock.

Spreadsheet Data 1

In cell D12, the number of hours for Monday, we type the following formula to calculate the number of hours; =HOUR(C12-B12)

This will give us the number of complete hours difference between the start (B12) and finish (C12) times. For Monday this is five hours. To calculate the number of minutes we type the following formula into cell E12; =MINUTE(C12-B12)

This gives us the number of minutes. So for Monday, we worked five hours and forty minutes. From half eight in the morning till ten past two in the afternoon is exactly five hours and forty minutes. Copying these two formulas down for the rest of the week gives us the number of hours and minutes for each day, as in Spreadsheet Data 2.

Spreadsheet Data 2

We now have the number of hours and minutes worked on each day, along with the rate. We can now calculate the amount chargeable for each day. In cell F12 we write; =(D12+(E12/60))*$B$8/$B$9

In English this reads as: Add the number of hours to the number of minutes (as a proportion of an hour), then multiply this by the hourly rate, then divide this by the number of hours the rate covers.

We surround the cell references B8 and B9 with dollar signs so that when we copy the formula down the rest of the week, the references don't change for the rate information. Spreadsheet Data 3 shows the formula copied down the rest of the week.

Spreadsheet Data 3

Now we sum the amount for each day to give a weekly total. In cell E8 we write; =sum(F12:F16)

This completes our timesheet, as in Spreadsheet Data 4.

Spreadsheet Data 4

The sample timesheet with some extra formulas and formatting is available as a free download. Remember to always virus scan any file you download from the internet. ..back to top