How to Track Your Work Hours in a Cross-Functional Team

Have you ever noticed how much of the world economy is devoted to counting? We count how many things we have. That’s called inventory. We count who owns what. That’s called money. The entire banking industry counts other people’s money, and takes a big chunk of it as their fee. That’s called finance. We count time on time sheets, purchases on expense reports, and Desperate Housewives on TV. But counting quickly gets repetitive. That’s why we have totals. We like counting, but we LOVE totals. And sometimes, we need to count the same thing in different ways to give it to our total-loving bosses. That’s the quandary that Kim finds herself in. She wrote in asking, “How do I track my time in a cross-functional, cross-department organization?”

Kim, welcome to my fantasy world. In my fantasy world, you work in the Human Resources department of a company that produces two things: Silly String and Bouncy Balls. You have two major functional areas, Accounting and R&D. Your job is to provide counseling to the employees to help them come to grips with the fact that their abusive Vice President isn’t going to be fired any time soon, because she just married the CEO’s son. These are the counseling sessions you jokingly call your “hope destroying” sessions. Only you’re not joking, are you?

How to Track Time Using a Grid

One way to get quick totals of your time is to use my favorite tool: a grid. Label row 1 “accounting,” and row 2 “R&D.” Label the first empty column Silly String and the second column Bouncy Balls. Now at each intersection, put how many hours you spent destroying the hope of employees in that function and division. Er, I mean, helping those employees. For example, say you spent two hours with the accounting people for Silly String and three hours with the accounting Bouncy Ball people. Add up each row to find out how much time you spent in each function. In this case, the Accounting row would show a total of five hours. Then sum each column to find out how much time you spent with each division.
 
This seems like a wonderful solution, until the next day. The Silly String accounting people just overdrew the checking account, and of course, they’re blaming the Bouncy Ball division. “Your elastic is seeping into our accounting systems and making our checks bounce!” It takes you an hour to calm them down. When you go to your grid to record the time you spent, you already show 2 hours in the Accounting/Silly String intersection. You can add your extra hour and change this to a three, but that seems like so much work. And it means you have no record of the fact you actually visited the Silly String people twice.

How to Track Time Using a List

Using a list instead of a grid will give you the level of detail you want. Create a blank spreadsheet and label the columns: Hours, Function, Division, and Activity. Enter a new row listing the number of hours, the function, the division, and what the activity was for every instance. Using the earlier example, you would list 2 in the hours column, Accounting in the Function column, Silly String in the division column, and Hope Destroying in the Activity column. For your second intervention, you would list 1 hour, Accounting function, Silly String division, and Calmed the Restless Natives in the Activity column.
 
Now you can use the list to find the time you spent in any given function or activity. For this, use the Autofilter spreadsheet function. I discuss Autofilter extensively in How to Plan a Big Event. In Excel or OpenOffice, choose Data | Filter | Autofilter. In Numbers for the Mac, choose Reorganize. Little arrows appear for each column. Click the arrow on the Division column and up pops a list of all the divisions—Silly String and Bouncy Ball. The Function arrow gives a list of all functions—Accounting and R&D. Then select the division or function you want, and all non-matching rows will vanish faster than a bald spot on a Rogaine commercial. You’ll only see a record of the hours you’ve spent doing work for that function or that division.

Copy and Paste to Sum the Numbers

Now you’re viewing just the hours you want. You still need a total. A SUM formula won’t work, because even though the non-matching rows are invisible, they’re still there in secret. To tally up your total, select the column of visible numbers, choose Edit | Copy, and then paste that column in a blank area of the spreadsheet. Now create a formula that sums up the numbers in that part of the spreadsheet and you’ll have a total for the hours you spent.

For you who are brave, courageous souls, an even better tool for slicing and dicing your data is available but not as easy to use. In Excel, it’s called a Pivot Table Report. In OpenOffice, it’s called DataPilot. If you need to examine your data in ways more flexible than a grid or filter will allow, check out PivotTables and DataPilot.
 
To recap: to track your hours by both department and function, use a two-by-two grid. If you want to track individual activities that sum up to your total hours, use a spreadsheet table, combined with AutoFilter to get different views of your data. If you want even more power in how you delve into the numbers, Quantrix and ModelSheet may be the tools for you. If your time tracking is successful enough, you’ll be able to hire someone else to do it for you, and can turn your attention to more important things, like watching “Desperate Housewives.”

Work Less, Do More, and have a Great Life!

Coworkers image courtesy of Shutterstock