Hey there coda community,
I found coda a month or so ago and felt it could be a great platform to move my “freelance activity tracker” to. Originally I developed it in Google sheets. It works there but requires some manual labour. I am hoping to get it fully automated in coda and so far it’s working nicely.
The core of my system is not very complicated. I have a “Time Input” table where I input how many hours I’ve worked on a project/job each day.
From there two main things need to happen:
- -work hours for each project are automatically added together so I can easily see the total hours I’ve worked on a particular job.
- -work hours for each month are automatically added together
I was able to hack around and get the features working. But I am not much of coder so I am not sure when something will break 
So I thought I’d see if an experienced coda user could take a look and let me know if I’m doing things the right way.
The main things I would like to check are
- -The formula in Projects 2019 > Active Time
- -The formula in Monthly Activity > Total Time
- -In Monthly Activity > Month I would like to generate the month names automatically. My approach is a bit hacky (like many things I do). What’s a better way to do this?
- -under Time Input I am going to have potentially hundred of entries (a couple of inputs daily). Would that be a problem?
There are other features I’d like to add but those are really the core ones. Cheers!
3 Likes
Hi @Todor_Nikolov_ToshiCG!
Welcome to Coda! Your setup here is a nice and simple way to track time against projects. I like how it calculates you “rate” as a form of contract over time_spent.
I only found one thing that I think is an issue, and it is an easy one to fix. Watch what happens if you try to update the name of a project:
You’ll notice that the name does not update in your Time Input table, and therefore your Project’s formulas also do not function as expected.
If you change two formulas (and your perspective on Coda data-linking), this can be addressed. See the improved Coda Example:
In the Project column, the available options should be the actual rows of the Projects, not just the name. Coda intelligently grabs the “display column” as the value to display, which is already your “Project Name” column!
==> “Selectable Options”: [Projects 2019]
Then, you have to change the formula in your “Active Time” column to change with the new selectable options.
==> “Active Time”: [Time Input].Filter(Project.[Project Name]=[Project Name]).Time.Sum()
I will finish by saying that there is even a better formula that is more inline with Coda thinking. In column formulas such as yours for “Active Time” , the formula is applied at every row to calculate the appropriate values. To make things easier to think about, the developers have added the nice notation: “thisRow”. With that in mind, we can acknowledge that “thisRow” is in fact the project, and each select is one of these projects! As such, here is the simplified formula.
==> “Active Time”: [Time Input].Filter(Project=thisRow).Time.Sum()
To answer your other questions:
- Your other formulas, even though they feel “hacky”, are actually written the same way I would. I think they are fine.
- The question of performance is only one that we can answer with time. If you notice a slow-down in your performance, perhaps you need to duplicate your Coda doc every year or so, and start the data over again. For now, I would not worry about it.
I hope these explanations make sense! Good luck making more great Coda sheets to simplify your life.
Lloyd
5 Likes
Hi Lloyd
thanks a lot for the help. I appreciate it!
I got the Project Names working. That’s a great point that I didn’t originally consider!
On the other hand I cannot get Active Time to calculate correctly now. It’s late now so I’ll give it another go tomorrow. I am sure I’m missing something obvious.
I like how it calculates you “rate” as a form of contract over time_spent.
Yeah, that’s one of things I like to track. Actually this is another area that I would like to automate.
There are two main type of contracts: “fixed price” and “per hour”. I was wondering if I could have a column Contract Type that controls how rate and earnings are calculated.
-for “fixed price” Earnings are input manually. Rate is calculated =Earnings/time_spent
-for “per hour” Rate is input manually. Earnings is calculated =rate*time_spent
Would that be possible? At the moment I don’t see how I could achieve it. It seems like each column can be either defined as a formula or direct input but not both interchangeably.
Active Time calculation works now!
I hope to get advice on “fixed price” vs “per hour” switch and if it is possible at all.
Hi @Todor_Nikolov_ToshiCG,
My advice for what you seek in the “fixed price” vs “per hour” is to have two different columns. They can be combined into a third column that has an “If” and summarizes the values for you. This means, however, that you really have to know what kind of information you want displayed.
Check out this page I made that showcases what you can do along the lines of what you just asked for:
Let me know how this looks!
Lloyd
1 Like
Hi Lloyd. Great, that looks awesome and makes sense!
I was considering adding more rows, nice to have the idea confirmed.
So I guess I’ll add as many rows as needed to get things working. Then hide all that the user (me) doesn’t need to see… or create a “view”. Not sure yet but I’ll give it a try.
Maybe I am getting ahead of myself but I think it should be possible to add buttons that allow me to edit the hidden data.
OK, I’ll try to get things working in the way I want and show an update later
This is a great tool @Todor_Nikolov_ToshiCG! I would’ve loved a tool like this when I was freelancing. I was also using Google Sheets to track my time and projects, and like you said, had hacked it together even though I knew it wasn’t a great solution.
I think @Lloyd_Montgomery addressed most of the questions you had regarding making sure the project name is linked in all places, and how you can calculate fixed price vs. per-hour projects. For the time input, you mentioned you might have several hundred rows. Performance may be an issue once you get into the thousands of rows, but what can speed things up a lot is to have your master time input table in its own section, and then have a view that filters only for the latest 30 days of data so you can still get insight into your data, but not see the entire table going back to the first row.
I also noticed you have a Progress column in the “Projects 2019” table. Do you plan on updating this manually, or will there be a formula there. This might complicate your doc, but perhaps you could have a separate table that is called “Project Tasks” where each row is an individual task you need to complete for a given project. Then, in the “Projects 2019” table, you can automatically calculate your progress based on the the number of Project Tasks you have completed divided by the total number of tasks for that Project.
1 Like
Hey @Lloyd_Montgomery and @Al_Chen_Coda .
Thank you for your valuable input! I am happy to say that the doc is really starting to behave as I want it to.
The Amount Earned/Rate issue is solved. My approach is a bit verbose but it was easy to write(there are 4 hidden columns for that). I added an “Edit” button that allows me to edit the hidden data if I have to.
I also added columns that automatically track the beginning and end of a job by fetching the first and last time entry.
And a Deadline column and a calculation for Time Left.
So things are looking pretty good to me. If you’d like to take one more look for possible problems I’d appreciate it.
@Al_Chen_Coda Thank you for the info on performance. At this point I’ll probably manually reset the system once a year. So hopefully I won’t run into performance issues. But if I do I’ll give your suggestion a try.
As for “Progress” I just want to update it manually. It’s meant to reflect my “feeling” for how far along the job has come. I understand you idea and I think it’s very cool. But it will probably introduce more complexity than I am ready to deal with now.
1 Like
Sounds great @Todor_Nikolov_ToshiCG! One suggestion for the Progress column if it’s just your general “feeling” about the project. Consider changing the column type to a Scale format which gives you a visual indicator on your progress for the project:
3 Likes
@Al_Chen_Coda thanks for the suggestion. I was considering either Scale or Slider, will see what feels best.
Somewhat related, is there a way to create a “gradient” (e.g. to color a row or cell) based on percentage/scale/slider value? The classic example is red for low values, gradually shifting towards green as values increase. I looked into Conditional Formatting but there is no obvious gradient option.
There isn’t a native gradient option based on the slider value at the moment. You would have to set up your own rules in the conditional formatting menu (something like 0=red, 3=orange, 5=yellow, 10=green).
Thanks! OK, I’ll do that. A gradient option would be very cool though 
2 Likes
