“Recording Worked Hours” (by Client/Project)
We all like free stuff don’t we? Especially if it proves useful! Here on this page, I’m offering you free of charge a useful spreadsheet template I created for recording hours worked.
“Recording Hours Worked.xlsx”
It’s a spreadsheet I’m using myself so I’m getting first-hand experience of using the file. In that way, I hope you also find it beneficial.
No strings attached!
Though coffee and a doughnut would be nice!
If you like it, let me know. If you share it with others, please let them know where the spreadsheet came from!
It’s probably most useful for those self-employed people out there who need a simple way of recording hours worked. It’s intended for recording hours worked for their clients, but it can be used for other business scenarios such as projects. If you use it in larger scenarios, I’d be very interested to find out how. Would you let me know?
All I ask is that you sign up to the Anything I.T. mailing list or Like our Facebook Page. We don’t spam with numerous emails. In fact, you will likely only get between 1-2 a month! I’m not even going to force you to provide an email address. The file is just there, at the bottom of the page, ready to use.
To make this work, you may need a basic understanding of Excel documents. if you break your copy, you can ask me for help, but please don’t blame me!
Explaining the Document
Below are a couple of screenshots of the SUMMARY sheet of the spreadsheet (or workbook whichever you like to call it). You will notice there are a number of sheets, all named the months of the year, as well as the SUMMARY sheet.
On this, the SUMMARY sheet, we enter the client/project on the left-hand side. These names are automatically populated to the other individual sheets. This is important as it ensures no typing mistakes are made by the user (and thereby providing accurate data).
The SUMMARY sheet provides not only a, ermmmm, summary, of hours worked each month and for each client/project, but money made (if you use this as the basis for your invoicing).
The financial aspect is optional and for me at least, it is primarily used for giving me an idea of where I am with certain clients.
You will also note the columns which I hope are self-explanatory:
- Total Hours
- Total £
- The individual months (each with Hours and £)
On the far right-hand side, you will see a list of rates. Enter your own here. These rates are used on the individual month sheets (more on this below).
The ‘Month’ sheets – Entering in your hours worked
On each ‘month’ sheet, you should see on the left, the area where you need to enter four pieces of data:
- Date (the date of course!)
- For Whom/What (See below for an explanation)
- Description (Whatever you want in here to describe the work carried out)
- Hours (The number of hours obviously. Can work in parts of hours too, such as ‘1.5’.
Your client name or project name. Clicking into this cell reveals a dropdown arrow which picks up the client names/project names you entered in on the SUMMARY sheet. Adding a client name or project name on the SUMMARY sheet automatically adds it to the dropdown options.
This cell is configured so that you must enter in (or select from the dropdown) an exact match from the list. Trying to type in ABC1234lhalhdlahdl (if it doesn’t exist on your SUMMARY sheet) will produce an error.
Type in a description and enter how many hours work, or indeed a decimal of an hour (0.1 would be 6 minutes, 0.25 would be quarter of an hour, 0.33 would be 20 minutes, 0.5 half an hour, and so on).
The ‘Month’ sheets – Client/Project auto-summarisation
On the right-hand side, you will notice the client names (or your project names) automatically populated.
Important: Do not try to type into these cells. Only the rate column of cells is editable (but you will still need to enter valid data).
Additionally, the hours worked for each client/project is auto-calculated based on the data on the left-hand side so do not try to edit this directly.
Option: You can also choose a rate for this client/project. If you do, then the amount in currency is automatically calculated. It could be useful for you if you need to generate invoices based on time worked.
The rate is also chosen via a dropdown menu. The rates are configured on the SUMMARY sheet to the far right-hand side.
Like the other dropdown, you must select a valid option otherwise it will display an error.
In the blank version below, some cells are protected. This is because we don’t want anyone accidentally overwriting the formulas within the cells by mistake and wondering why it’s not working!
If you feel competent enough, you can unlock the cells via unlocking the spreadsheet by clicking the ‘Review‘ tab on the Ribbon and clicking Unprotect Sheet and entering the password doughnut5 (hint hint).
Potential Future Enhancements
I’m considering adding the following columns on the ‘month’ sheets next to the amount (£) column:
- Invoice #
- Sent (date)
- Paid (Amount £) – Colours Red (Unpaid), Orange (Partially paid), Green (Fully paid)
Colours of cells would change depending on invoice status, Sent, and Paid column.
Summarisation and Downloads
So, I hope you find this spreadsheet useful. I certainly do!
There’s a blanked out version to download below as well as one containing some sample data.