PM Tools: Time Tracking/Budgeting

[eskufca (Emerson Skufca)]

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.

I originally posted this here.
Overall, I’m wondering if anyone has built out any similar systems or tools in this platform. I’d really like to get the system as outlined below functional…but I may be edging towards the realm where I need to build out an app and/or hire out some support. Any insight or advice is greatly appreciated!!

~ ~ ~

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…

[basdebruin]

Hi @eskufca

To answer your “million-dollar question” I think you should look at the Master-Detail app video I posted here Master-Detail app available on August 29 - #6 by basdebruin

I (and as I believe many others) are struggling with aggregating columns (like time tracker) in X amount of Projects into a “Project overview board”. Dashboards are really good looking but there are some limitations (max nr of boards being one of them). The overview widget in Dashboards do not provide time tracker information, nor other number columns used for costs, estimated time etc.

Subitems can also play an important role, but when each project is a (large) set of subitems it can become a little messy. Furthermore there might be situations that the project manager for one or more specific projects does not need to know some information.

With Master-Detail each item in the Master board represents a (Detail) project. You can build columns for project properties (like who is the PM) and then you can build columns that (with the help of recipes) aggregates number, statuses, timelines and time trackers. These aggregated number will be kept in sync by webhooks in the Detail boards (that are added by switching on the Detail project in the Master board).

I am considering to create the (Detail) project boards automatically when you add a row to the Master Project Overview and I am very interesting to learn your thought on that. You can - off course - create a template for these (Detail) project boards, but it is kind of hard to decide where to store these board when created automatically (which workspace etc).

Let me know what you think of this.

[eskufca (Emerson Skufca)]

Hello, @basdebruin. Thanks for your message! I’m really happy to have some support in thinking through how to achieve my needs for this tool on this platform.

I’ve taken a look at your posts about Master-Detail and watched the video. I have one main question or potential concern. It appears that in order to use this app for time tracking on projects that we would need to create a different (detail) board for each individual project. It seems like then people would need to go input their time in multiple different boards (most people work on multiple projects at once). The overall intent of what I’m trying to do here is have a central location (ideally a single board or form) where someone could go to input time to any number of different projects.

Am I on track in my thinking here (regarding the application of your app)?

[basdebruin]

Hi @eskufca

Thank you for having a look at Master-Detail app. I now have a much better idea of what you are trying to achieve, and to be frank Master-Detail is not built for that use case. It is more to “report” and aggregate information form a number of project (details) in to one overview board (master).

You use case is appealing to me, there must be other users trying to do the same. I will start to investigate what is possible here. Something along the lines to watch a number of project boards and extract the items for a particular user where he/she can enter time for the tasks he is assigned to across those projects. Interesting use case.

[eskufca (Emerson Skufca)]

Thanks again for your input and thought on the matter.

Just to clarify a couple of points about my intent and specific application:

• I’m actually envisioning either one central ‘Time Tracking’ board (as outlined in my original post) or perhaps even one such ‘Time Tracking’ board per team. Not an individual board per project.
• See the first screenshot in my original post for an idea of the info I’d intend to collect. This board links in a list of possible projects to bill time to from our main ‘Projects Overview’ board.
• Aggregating the data into monthly buckets and showing a project total (like the pivot table does in the second screenshot) is very on track with what I want to do.
  The ultimate goal is to compare (time spent)(billing rate) to budgeted Fee, which is planned out in the ‘Projects Overview’ board. The main issues come in due to: (1) formula columns not being supported in the native pivot app and (2) not being able to directly reference the items in this pivot app back to the budget values in ‘Projects Overview’.
• I’ve fiddled around in Dashboards trying to use a visualization tool that pulls from both boards (budget info from ‘Projects Overview’ and aggregated time-spent-in-dollars from ‘Time Tracking’) but it seems to overload/confuse/crash the widgets/apps.

I would imagine many many other project managers and various Monday.com users would find a tool like this very helpful…and seems like a natural thing that would be great to do in this platform.

[tpaktop (Maxim Filimonov)]

Hi,
Just wanted to chime in and mention that you can achieve the same functionality with native monday.com formulas.
Here is an example from our own board:

Actual Time Estimated is being pulled from details boards and aggregated automatically. This is all using new dual linking feature - released about a week ago.

The only issue there is a bug which has been reported to Monday.com team already. Normally you can extract information from time tracking column in formulas using #Hours but it doesn’t work properly for mirror columns.
So we need two formulas:
Estimated Time(minutes) which just takes time from hours and multiplies it by 60 * 60 to get value in minutes.
Then you can easily compare that to value in tracking column using a formula like
IF({Actual Time Spent}>{Expected Time(minutes)}, “Overbudget”, “OnTime”)

To make it display different colors we just use conditional colouring native monday.com support.

[eskufca (Emerson Skufca)]

Hrm, intriguing! Thx for the heads up @tpaktop.

Do you have any more info about the new “dual linking” feature (I just noticed “two way linkage” referenced in a new features webinar update)? I’m intrigued to test that feature.

As for the time tracking, I’m not really tied to using the ‘Time Tracking’ type column…I was thinking of just having people input via a numbers column manually. I will definitely want to be using formula columns to convert to dollar amounts somewhere…but I’ll get to that after I figure out how this new linking works…

[eskufca (Emerson Skufca)]

@tpaktop - I just linked the two boards (‘Projects Overview’ and 'Time Tracking) together again and noticed that it did include a new feature that added linked columns to both of the boards. I can (still) select which project to associate time with from the ‘Time Tracking’ board…and when I go back to ‘Projects Overview’ it shows me which items have that project selected in the ‘Time Tracking’ board…but I don’t see how to get it to sum the values of a specific column. Can you elaborate some on how you’ve got your tool set up?

[tpaktop (Maxim Filimonov)]

I will record a video of our full setup today. Will post it here.

[eskufca (Emerson Skufca)]

Thank you so much! Very appreciated.

[tpaktop (Maxim Filimonov)]

Hi,
Here is the video
Let me know if you have any further questions.

[eskufca (Emerson Skufca)]

Thanks! I’ll look into it a bit more today and let you know if I have any further questions.

[eskufca (Emerson Skufca)]

@tpaktop

IF({Actual Time Spent}>{Expected Time(minutes)}, “Overbudget”, “OnTime”)

I’m having trouble getting something like the above formula working. Do the > and < operators really work in formulas? It’s showing ‘illegal formula’ warnings.

Screenshot:

I’ve tried both of the following:

• IF({Amount Spent}>{Net Fee}, “Overbudget”, “OnTime”)
• IF({Time Spent}>{Est. Hours}, “Overbudget”, “OnTime”)

Thoughts, @tpaktop?

[tpaktop (Maxim Filimonov)]

It’s a copy/paste issue
When you copy from this forum all "(on us keyboard press shift + '(left from enter)) turn into “ I know they look very similar but it’s what is causing the issue.

I would recommend typing it yourself by just recreating formula.
I recorded video of progressive formalisation technique that we use:

[eskufca (Emerson Skufca)]

Ah, thanks for flagging the issue w/ copy/pasting quotes. I’ve gotten it working, along with some conditional coloring.
[quick note: the sound wasn’t coming through for me on the video above @tpaktop]

I’ve now come across something else that is puzzling me. I’ve implemented two formula columns: ‘Fee Check’ and ‘Time Check’. They both seem to be working, but the ‘Fee Check’ column is showing an error for items which don’t have any ‘Amount Logged’ data. I’m copying screenshots and formula column code for reference. It’s not a major deal but just curious why the two don’t behave the same way for items which don’t have linked data.

‘Time Tracking’ board:

‘Projects Overview’ board:

’Fee Check’ formula column code -> IF({Amount Logged}>{Net Fee},“Over Budget”,IF({Amount Logged}>0.8*{Net Fee},“Approaching Net Fee”,“On Track”))

’Time Check’ formula column code -> IF({Time Logged}>{Est. Hours},“OVER”,IF({Time Logged}>0.8*{Est. Hours},“Approaching Estimate”,“On Track”))

Any thoughts on why the the items which don’t have any linked data aren’t just defaulting to “On Track” in the ‘Fee Check’ column?

[tpaktop (Maxim Filimonov)]

[quick note: the sound wasn’t coming through for me on the video above @tpaktop]
Indeed don’t know what happened there

@eskufca

’Fee Check’ formula column code → IF({Amount Logged}>{Net Fee},“Over Budget”,IF({Amount Logged}>0.8*{Net Fee},“Approaching Net Fee”,“On Track”))

I believe, it’s a bug but I have found a workaround.
To make sure that number gets converted to default 0 we need to use a function which will force it to turn into number. When mirror columns doesn’t have linked values i do believe it defaults to something like an empty array. While as soon as it has values it sums them up.
If we become explicit about this summing up it forces even empty array to become zero.

To sum up whenever you use a mirror column which is summing up certain numbers use SUM function to make sure that it will default to 0 when there are no links established yet.
New formula would become:

’Fee Check’ formula column code → IF(SUM({Amount Logged})>{Net Fee},“Over Budget”,IF({SUM(Amount Logged)}>0.8*{Net Fee},“Approaching Net Fee”,“On Track”))

Hope monday.com team will fix it in the future.

[eskufca (Emerson Skufca)]

perfect, thanks for the workaround!!
-Emerson

[eskufca (Emerson Skufca)]

update on this thread for anyone who wants to follow my saga in trying to get this to work for me