Tracking Every Second in "Wick" - A Timekeeping Tool Made in Excel
During production of my team’s capstone game at FIEA – Wick – I wanted to keep a written record for how long every asset and task would take, for the sake of sprint planning.
As you can most likely guess, this record would quickly grow into a vast database of entries and values. Making this as user-friendly as possible without compromising on its utility and power was bound to be a challenge.
To start with, I wanted to ensure that we were getting the most accurate time data possible for how long tasks took. So, my project lead, Zach Karlins, and I integrated Toggl – a time tracking tool – into our workflow. Then, I made sure every member of my team had Toggl’s Chrome Extension installed, so they would merely need to press the corresponding button in Jira to track their tasks.
At the end of each sprint, their data gets dumped into this database I made in Excel.
Front Page
The dashboard is meant to be a one-stop shop for finding exactly a certain kind of data, from a certain particular point in time, from a particular person or group of people. I accomplished this interface by setting up a pivotable that pulls from a chart in another tab that has all time data across all our sprints so far. More on that later.
This pivotable is then configured to show the day a task was logged, followed by the person who logged it, and then the sum of their hours by day, person, and individually for the task.
Since we made sure to utilize the Toggl chrome extension paired with Jira, every task performed in Jira is logged via Toggl with the corresponding issue code. This allows me to quickly find the issue in-question for a time punch in case there’s an outlier with my data, or if I want to audit the validity/source of the task.
I then assign broad categories to each task that is logged – so I can see every task that is tagged as a meeting, programming task, art task, etc., and filter my data via these slicers accordingly.
In just three clicks, I’ve found every single 3D modelling task done by Paxton – one of our artists – during Sprint 2. I can further narrow down into individual dates or days.
To further streamline going back and forth between filters, I also added a “Clear all filters” button, as checking records while estimating means a lot of flipping between people and disciplines. Excel lacks a function to clear all filters quickly out of the box. In other words, each time I want to restart my query, I’d have to undo all my slicers.
So, in response, I wrote a snippet of VBA (Visual Basic) code to the button that handles all the functionality for me. Here’s what it looks like:
Another metric I wanted to track on this front page is our work time vs. meeting time. This is accomplished using a series of =SUMIF statements in cells J and K.
Since each task has a set tag, I can use that data to my advantage to quickly create dynamically-updating displays such as this. The “Work Time” cell uses a similar =SUMIF statement, but also using the “does not equal” operator:
Why the “does not equal” operator is <> and not != like literally every other programming language on the planet will forever be a mystery. Sigh.
Currently, this shows the meeting and work time for all members of the project thus far. In the future, I’ll most likely expand this section to do a dynamically-updating breakdown of the meeting-to-work ratio for each sprint.
All-Data
Let’s move over to the “All-Data” sheet, where the data for this table is pulled from.
This massive table is where all the data lives that the dashboard pulls from. While it seems threatening at first, it’s really not so scary.
At the end of each sprint, all the time data is exported from Toggl as an XLSX file. I then format that file accordingly, and copy-paste into this sheet, where it is formatted to conform with the rest of my data. Then it’s just a matter of marking them all as “Sprint x”. Because my table is frequently filtered by A-Z, Excel does all the sorting for me.
Notice the green, yellow, and red columns over on the right side. I made those columns to further help guide the time estimation process, and each have formulas to automatically create some sample time estimates.
The green column is a figure for a low-end estimate of a task – in the event the artist/programmer/designer is faster than others, or the asset is simpler. It is simply 75% of the total time the original task took.
The Red column is a figure for a high-end estimate of a task – in case the person working on it is slow, or the team anticipates some unique quirks with the asset that will prolong how long it takes. This is 125% of the total time the original task took.
Finally, the Yellow column is the median estimate – a “middle of the road” estimate that serves as a relatively safe estimation figure to base future estimates. This is simply the Low end, high end, and total time added together, then divided by 3.
Results
There are other tabs in this sheet that I used as grounds for collecting other data, such as the aggregate and average time spent across artists, programmers, and designers each week. These ended up being very useful for quickly visualizing data into charts for status presentations to the faculty and other students.
But the biggest impact this left was the ability to have a database of historical data on how long tasks are taking, so estimation decisions can be made data-driven and autonomous.
In other words, we don’t have to have a huge meeting for estimation of tasks during sprint planning. I can set the estimated time on the tasks, based on similar past assets, then do a sanity-check by the project lead and/or corresponding team lead. Finally, a due date is assigned and checked by the artist/programmer/designer assigned that task.
This helps expediate sprint planning and eliminate the majority of back-and-forth conversations about tasks, and it’s definitely helped our team so far.