Why I built this

During my two years as the manager of a market research team, a significant portion of my time was taken up by things you could call “HR duties”: hiring, training, reviewing work, and so on sometimes took up to half of my week. In addition, since my interns were payed on an hourly basis, I also collected and managed records for time worked.

When I joined, the company subscribed to a QuickBooks product which provided user-accessible forms and automatically-updating tables for tabulating contractor pay. However, the software’s poor communication with the company bank account introduced delays in pay, which we usually had to correct manually. Soon after I joined, we could no longer justify the high subscription costs for such an unhelpful product, and canceled the subscription.

Since the interns were students, they worked irregular hours, which is a pain to tabulate by hand. For a few weeks, the interns reported hours via message to me, which I input by hand into a spreadsheet. However, this was time-intensive, since every day brought a handful of new hour reports that I needed to carry over and add into to my “official” hour log spreadsheet. In addition, a few times I even miscounted total hours due to a message that fell through the cracks on a busy day. (You know how inboxes are, I’m sure.)

To prevent this drain on time and reduce the risk of human error, I created an hour logging system using Microsoft Excel in the cloud. Here, I’ve recreated the tool using the Google Sheets and Forms, given that Office cloud applications have a paywall.

Demo

I will walk you through the form and the results spreadsheet. After that, I encourage you to try it out for yourself by reporting some hours on this demo form, and checking out how they automatically report in the demo results sheet.

The Form

The contractors reporting their hours can input them using a simple online form. I’ve created an example form to help a certain agency to help manage their arrangements with Earth’s Mightiest Heroes:

(If you want to open the form in a new tab or you cannot view it below, click here.)

Using the Form

As you can see, logging a work session is simple. There are 4 fields:

• Name – You can input from a preset list of eligible employees’ names. Working from preset values allows for easy matching to each employee’s total when we are tabulating totals.
• Date – Important for tracking hours as an employer, and getting employees paid on schedule!
• Number of Hours – Rounding to the nearest quarter hour was just policy for where I worked. You can round more or less specifically if you choose to deploy a form like this!
• Short Description – Only optional field here, but can be required. Just to help give the employer a little context!

This is a Google Form, but originally I made the survey using an Excel Survey.

The Hour Log (“Reporting”)

There are 2 sheets in the results workbook, “Reporting” and “Totals”.

“Reporting” shows a simple record of the form input. As you can see, there is a column corresponding to every field in the form.

The payroll manager, who should have direct access to this sheet, can adjust the record if they believe there to be an error. A few examples:

• If an employee accidentally enters incorrect information, you (the payroll manager) could easily pull up the spreadsheet, check for the entry in question, and change if necessary.
• If you wanted to add bonuses, you could add a line with the number of hours equivalent to the intended bonus.
• If some reporting looks fishy, you can delete it. Based on the “Short Description” on line 5, it looks like Iron Man is trying to collect pay for time he spent fighting Captain America in Civil War... We probably don’t want to incentivize hero-on-hero fighting, so maybe we could delete that item.

Viewing Pay (“Totals”)

The other sheet, “Totals”, automatically totals the number hours for each employee in each pay period. Each row corresponds to a pay period – here, the Avengers are paid each month, but this can be adjusted by selecting different dates for columns A and B. You can add or subtract rows for more or fewer pay periods.

Each column corresponds to an employee. You can add or remove columns at will to add or remove employees. However, the names should also be added or removed as options from the Google Form. Names in the column header must match options in the Google Form exactly.

The highlighted cell, B14, is the key input on this sheet. The number here tells the sheet which row to report. In this image, I have selected row 11, which corresponds to the pay period October 2020.

The sheet takes the total hour count in the selected row, then multiplies it by the hourly wage. (Here that wage is $12, which I suspect would be inadequate compensation for the Avengers in real life.) The result is shown in row 15, “Pay for Selected Month”.

Try it out

Take a look at the Google Sheets workbook here. Most of the workbook is locked, but you should be able to edit cell B14 on the “Totals” sheet – play around with that, noticing how changing the row changes the reported values in row 15!

To see it in action, enter some sample hours yourself and see how that changes the pay results! After submitting the form, check the “Reporting” sheet for your test input. Then, go to “Totals” to verify that the number is added into the correct cell.

How it Works

1. Inputs on the form are ordered chronologically on the “Reporting” sheet.
2. The cells in white on the “Totals” sheet use the function SUMIFS to sum reported hours with (a) a name that matches that at the top of the cell’s column and (b) a date that falls between the “Period Start” and “Period End” values for the cell’s row.

3. Every cell in Row 15 uses INDIRECT to select a cell above and copy its value. It gets the row number from cell B14, which we can adjust freely. (In the formula, char(column()+64) gets the letter value for the column that the cell is in, so that any results will be for the appropriate employee.)

In general, all the formulas used should be the same between Google Sheets and Microsoft Excel.

Conclusion

I hope this model can help you create a free hour logging system for your own organization! You are welcome to use the Google Form and Sheet provided as a template for your own purposes. If you have questions about how the functions work, or your spreadsheet broke and you don’t know why, let me know! You can reach me in the comments below, or by email, LinkedIn, or Twitter.