Newsletter June 2010
|
- 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.
|
|
A simple Excel Timesheet
|
|
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.
|
|
|
Fig 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.
|
|
|
Fig 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 Fig 2.
|
|
|
Fig 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. Fig 3 shows the formula copied down the rest of the week.
|
|
|
Fig 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 Fig 4.
|
|
|
Fig 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 |
