Tracking Project Hours with Spreadsheet Magic

When I was growing up, I remember wanting to learn how to use spreadsheets. So I asked my dad to show me how to use Excel. He did, and eventually that is how I first started managing my money. Of course, a lot of people will use spreadsheets to make simple lists or just a few small calculations, which is perfectly fine. But there is also so much more to take advantage of with spreadsheets.

Spreadsheets offer a way to practice simplistic programming concepts without having to compile your code or, when making a tool for another user, meddle around with a graphical user interface. Plus, with the ability to lock rows and/or columns in a cell reference to effect how a formula drags, it makes for an easy way to expand the tool in the future.

Tracking Project Hours

I'm especially a fan of Google Spreadsheets and Google Forms. A couple years ago when I had to track work hours for a group project in Technical Writing, I used Google Forms to make a simple "check in" and "check out" feature. So long as everyone uses it appropriately, it was pretty good for keep a historical record.

It was kind of hard to calculate the hours, though, because a person's check out time was not always immediately after their check in time, especially if more than one user was using the tool. It took me a while to figure out how to calculate that with a single, drag-able formula.

Anyway, instead of boring you with the details, I'll just show you the tool.

Using the Tool

The idea is to make use of this tool as easy as possible. Google Forms allows a quick, simple, and structured way of allowing an individual to enter data into a spreadsheet from either a computer or even their smartphone. I keep another version of this form on my cell phone so I can log project hours, or work project hours, whenever I want to with just a few clicks.

Screenshot from Samsung Fascinate of the Google Form
from within Dolphin Browser

The data from this form spills into the "Input" sheet of a spreadsheet. In the image below, you can see that I'm still not perfect at using the tool, so I added a comments section in case I check in or out late, or as a means for just taking notes on what progress was made.

Screenshot of spreadsheet contents at the time of this writing

The next sheet ("Totals") is focused on calculating how many hours each user has contributed to each project. I've also added (in green font) an area to specify from which time range you want to total the hours. This is useful when I have to record how much work I have done for one of the many projects I'm working on for my Internship this summer.

Screen Shot of contents of total page contents at time of this writing

Well, feel free to play around with the spreadsheets using the links previously posted, comment, or ask me questions. Also feel free to make a copy of the template sheet if you want to suit it to your own purposes.

Formula

For the technically-minded who want to take a look at the formula, the below format is a bit easier to read if you are already familiar with formulas syntax in spreadsheets:

=24*
ARRAYFORMULA(
   SUM(
      IF(
         ISERR(
            (Input!$B:$B=D$1)*
            (Input!$C:$C=$A5)*
            IF(
               Input!$D:$D="Check In",
               -Input!$A:$A,
               Input!$A:$A
            )*
            (Input!$A:$A>=$B$1)*
            (Input!$A:$A<=$B$2)
         ),
         0,
         (Input!$B:$B=D$1)*
         (Input!$C:$C=$A5)*
         IF(
            Input!$D:$D="Check In",
            -Input!$A:$A,
            Input!$A:$A
         )*
         (Input!$A:$A>=$B$1)*
         (Input!$A:$A<=$B$2)
      )
   )
)

Note the use of the ARRAYFORMULA. You might want to read up on that on your own if you aren't familiar with it. If you remove the ARRAYFORMULA function (which is specific to Google Spreadsheets), you can enter the remaining formula in an Excel Spreadsheet (as an array formula) and achieve the same result. However, you won't have the added benefit of spilling a Google Form onto the first sheet.