Office Software

by Jonathan Jonathan No Comments

FREE Excel Spreadsheet Template – Recording Hours Worked

“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.

Disclaimer/Warning

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

Summary sheet

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.

Recording Worked Hours spreadsheet

Recording Worked Hours spreadsheetIn this example, we have a sheet that summarises the data across the year and the individual months.

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).

Recording Worked Hours spreadsheet

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’.

Recording Hours Worked

For Whom/What

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.

Recording Worked Hours spreadsheet

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.

Recording Hours 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.

Protected Cells

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).

 

Ribbon

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.

If you have ideas for improvements feel free to send a message via the contact page on this site, or via the Anything I.T. Facebook, or by directly emailing me: support@anythingit.net.

The files

Recording Hours Worked – with sample data.xlsx

Recording Hours Worked.xlsx

 

by Jonathan Jonathan No Comments

Don’t be scared of Excel

Yes, using Excel (other brands of spreadsheet software are available) can look a little scary. But don’t need to be!

Excel is a powerful piece of software that could potentially save you a lot of time, present information in useful ways, and highlight areas of your business that could prove extremely helpful.

You can make a spreadsheet to do very simple things, such as automatically adding up a list of invoice amounts for a given month, or keeping a running total of stock (numbers of products AND the total amount at cost or retail).

Attached at the bottom of this post is an Excel file with two tabs. One tab is a simple list of invoice numbers with dates and amounts. The other is a slightly more complicated stock system with values.

On the INVOICE tab I have also used ‘Conditional Formatting’. Conditional Formatting is a very useful feature. In the Conditional Formatting on the INVOICE tab, I’m colouring amounts of equal to or less than £25 with a pink background and red text. This is just to show you that perhaps there is a problem (i.e. should you have invoice amounts of £25 or less? Is there a mistake?).

The STOCK tab is perhaps a little more complicated but hopefully you get the idea?

I should say that there are systems out there that can do all this for you as well. Check out the likes of Quickbooks, Xero, Zoho, and many more! Of course, you may have to pay for these especially since they have a number of features (some of which you might now need!).

So, simple spreadsheets may help.

Additionally, spreadsheets don’t have to look ugly! Check out the attached to see what I mean.

Apologies if the attached isn’t an accurate representation of how these things work. It’s just meant to illustrate how spreadsheets can look and provide simple solutions.

Do you use Excel? Do you use online solutions like Quickbooks, Zoho? Let me know in the comments 🙂

Download the file

Subscribe to be notified of new posts

stay informed!

Subscribe to receive exclusive content and notifications