Using Spreadsheets to Collect Staff Hours

You may have some tremendous software that does all of your work for you, but many businesses want a solution to do the work the way they want it done and, very often, a spreadsheet is the answer. Some think spreadsheets are outdated, but they are actually evolving all the time and are still just as relevant now as they were 20 years ago. What other software can you say that about? Anyhow, if you wish to collect staff hours using spreadsheets, here are a few tips to help you do that more successfully.

The main point that I would like to make is that you should probably have at least two spreadsheets for this process, and they should be compatible with each other. One should be your office spreadsheet, which has all the sensitive data on, and the other should be the spreadsheet that you send out to your staff to collect the information. When I talk about them being compatible, I mean that you should be able to set up the first spreadsheet with all the data, copy and paste the relevant data to the second spreadsheet, and then repeat the process back to the original when the data has been returned.

Set up the ‘master’ spreadsheet.

This will have all the hourly rates (if applicable), the client names and contact details, and the staff names and contact details. It will also have the space for the daily times from the staff to be entered. This is pretty much a self-sufficient spreadsheet and, if you were filling the times in, would be all you need. This could then not only calculate who worked when, but also which clients had work done when, if you need that for your billing. The data could also be used for calculating reports such as the most worked days, working trends, most absent days, staff working patterns, etc. If the spreadsheet is made correctly, it should do most of this with you just required to add the times worked, and to keep the details up to date.

Send out the secondary spreadsheet.

The only real reason for the second spreadsheet is to keep the sensitive data secure. If you make the second spreadsheet with the same layout as the first, you can send the second to collect the required hours from the staff, and then simply copy and paste (using paste values rather than just paste) into your master spreadsheet, to reap the rewards stated above. The spreadsheet you send out needs to do nothing more than collect data. You could have some basic details from the master spreadsheet, but just leave the sensitive data on the master version only, this way ensuring that your sensitive data does not leave the office with the secondary spreadsheet.

If these spreadsheets are made correctly, you only need follow this simple process:

1. Update the master spreadsheet.
2. Possibly copy and paste one batch of data to set up the secondary spreadsheet.
3. Send the spreadsheet to staff to collect hours.
4. Copy and paste the collected data back to the master copy to populate reports.

This may sound like a complicated process, but if the spreadsheets are built properly, it can save you loads of time and effort. Many businesses still do this process manually. In fact, I have seen people still doing this with data emailed in text format, and then frantic calculations with calculators! This is often prone to mistakes, due to human error. If your process of collecting staff hours, working out wages, collecting information for billing, and reporting on working patterns, etc., is taking up valuable time and effort, then this may well be a solution for you. If your Excel capabilities are not quite at the level to handle this sort of template, please get in touch. That is what I do, and I love to do it.

All the best with your staff management.


Please share this if you think others will find it useful!