I’m working on developing a whole suite of project management, accounting, and business development tools within Monday.com for my company (a building engineering/services consulting firm). The item I’m currently wrestling with has to do with tracking time spent on various projects and comparing that aggregated data against planned/estimated hours or budgets.
The heart of the project management tools that I’m developing is a board that I’ve called the ‘Projects Overview’ board. This board keeps track of all of our projects split into groups of: yet-to-kick-off, active, and on hold. This is where all of our high-level and go-to information for every project lives: client info, deliverables tracking, timeline, invoicing, fee amounts, estimated time to be spent, team structure, etc.
I’m working on developing a system to track the time that our design team spends on any one project. My solution so far is to create ‘Time Tracking’ boards where individuals in the company can log time spent. This board template I’ve created has a place for people to log: amount of time spent (hrs), date time was worked, and a billing rate category. I’ve included a Link to Item-type column in this board so that people can select which project the time is to be associated with from the ‘Projects Overview’ board. (This is a very critical piece of the intended system architecture - to ensure that time is associated with actual projects and to avoid errors or duplicates).
I’ve also set up a couple of formula columns that calculate the total dollar amount attributed to the time spent (based on time input and a billing rate category). This is important because I’m ultimately wanting to be able to track these various cumulative amounts spend on various projects and compare it back to the budgets in my ‘Projects Overview’ board.
So this is where magic starts to happen and where I’m getting fairly stuck…
I can now have people submit new items to the ‘Time Tracking’ board and make sure it’s associated with the proper project linked from the ‘Projects Overview’ board. I can set up a pivot view from this board and have it automatically aggregate all of the hours spent on each project. This is a really great first step towards what I’m trying to do, but doesn’t quite get me where I need to be. See below for a couple of screenshots:
‘Time Tracking’ board:
And the pivot view:
Ultimately, the million-dollar question is about: how to aggregate this billed time per project in ‘Time Tracking’ and compare it back to budgets and estimates in ‘Project Overview’
The main issues I’m running into and questions I have:
- Formula columns are not yet supported in the pivot board view/widget app. One of the ultimate goals here is to tabulate and calculate the amount of allocated budget/fee we’ve spent so far. I’d really rather set up the pivot board to be summing the results of the ‘Amount Logged’ formula column to have a total dollar amount.
- I’m still trying to wrap my head around how I could potentially view this tabulated data from the pivot table and compare it back to ‘Net Fee’ amount that is held in the main ‘Projects Overview’ board. It would be great if there were some way to incorporate a Link to Item type column into the pivot board so I could include this informational reference column (and directly compare the summed logged time against the estimated/budgeted time).
- I’ve also mirrored the ‘Est. Hours’ and ‘Net Fee’ columns from the ‘Projects Overview’ board into the ‘Time Tracking’ board to see if there’s a way to creatively visualize this data like I’m wanting to.
- I’m tinkering with dashboard widgets, because they allow me to use link both the ‘Projects Overview’ board as well as the ‘Time Tracking’ board. But I haven’t found a solution that both aggregates and links the proper data in a helpful view.
I figured I’d post this all here in case others are wrestling with similar issues. I’d also be super grateful to anyone who has any advice to share about making this all work. I’m potentially interested in learning the programming skills needed to develop an app or something. Or maybe we could explore the ‘Hire out a Specialist’ option. I just want to know if what I’m trying to do is somewhat easily achievable…
Thanks for reading! I look forward to any insight…
