Hi guys, I’m really new here and struggling to wrap my head around the different formulas and what not. So quick backstory is I’m a carpenter and I’m trying to set up a document to help me with my jobs, from quoting and purchase orders, to a schedule/gantt chart and a list of all the tasks involved in a renovation etc.
Here is my struggle:
I’ve made a task list of everything involved and have start dates, end dates and have assigned each task to the respective trade such as the carpenter, plumber, electrician etc.
What I am wanting to do is calculate the total number of days the carpenter will be on the job, but my issue is there are some tasks that I have assigned on the same day as other tasks. So there are multiple entries on the same day so I can’t just simply add the duration together.
What I was thinking was that I would have to write something (please excuse my coding language or lack thereof… this isn’t my profession) along the lines of:
Sum(Tasklist.trades=“Carpenter”.If(Tasklist.Start=MULTIPLE VALUES and Tasklist.End=MULTIPLE VALUES, Count(First(Duration()))))
Really hope that makes sense. I feel a bit out of my depth haha!
Thanks in advance!
Joel
Thanks for the great question. This is a really common pattern and one where Coda can really shine, so I took a moment to write up a more generic answer (and compared it a bit to the spreadsheets approach).
Thanks Shishir,
I do get that but my issue is what if some of the tasks for the carpenter were to happen on the same day? For example, I might have the task “Wall Framing” and the task “Wall Straightening”. And if “Wall framing” started on 9/2/2019 and finished on 9/5/2019, but “Wall Straightening” started on 9/3/2019 and finished on 9/6/2019, then if I just counted the duration of each task I would get a total of 6 days. Whereas there is 9/3/2019 - 9/5/2019 where both tasks are getting done at the same time, but they aren’t extra days. How do I calculate my total days there, not adding extra days because of the allocated days for each task?
Hope that makes sense,
Joel
Then it would be advisable to add a column with “projects”
In this way you will be able to catch the project “start” / “end” & “total” days and still assign multiple tasks/day.
Even with the count function you will be able to display the amounts of tasks / day performed and from there an average/ week/ month etc.
Keep always in mind to define what is the expected outcome ( required info) you need?
Not fully understanding just yet sorry @Jean_Pierre_Traets,
As I said I’m a bit out of my depths with this but I’m trying haha!
I think I’ve already got a project table…
I just don’t know how to count the days.
Should I code it along the lines of =sum(tasklist.duration=ThisRow)
Sorry guys, I’m really not very good at this
Thanks in advance,
Joel
This is a simplified version, but I created two tables (Tasks and Categories). The Tasks table has start and end dates, and the Categories table has a formula for counting the number of days.
The formula looks for the first (i.e. earliest) start date and the last (i.e. latest) end date, and counts the difference between them.
Interestingly, I inserted a +1 because I could not find a formula akin to NetWorkingDays() https://coda.io/formulas#NetWorkingDays that includes weekends. Without the +1, the difference between the dates would always be 1 day short of the total duration. @shishir or others may have better insight on whether an alternative formula exists that I am overlooking.
This example can be expanded out to account for different projects and tasks existing together. For example, if you added a Lookup column to the Tasks table with the different Projects (e.g. Carpenter, Electrical, etc.), then you could add a filter to the formula to only consider matching projects.
This example may also require adding an If() statement or other method to account for no matching project existing.
If you need clarification on any of this, let me know.
