How to work out total days allocated to a job, if there are multiple tasks assigned to the same days

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

Dear @Joel_Worth,

Welcome :handshake:

In the table: “Task-1” you will be able to add new tasks and or make corrections accordingly your needs.

At the top you will be able to filter by:

  1. Done or Not done task
  2. Select one or more projects
  3. And select one or more as you call “trades”

In the duration column at the bottom you can see the sum of the hours selected

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).

1 Like

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

Dear @Joel_Worth,

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?

Does this make sense :handshake:

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

Want to see if I am properly understanding the issue.

If you have a project, and the carpenter has 3 associated tasks on this project, such as:

Task A, 9/1 - 9/3
Task B, 9/2 - 9/3
Task C, 9/2 - 9/4

The total you are seeking in this example would be 9/1 - 9/4, or 4 days (not 3 days + 2 days + 3 days = 8 days)?

Yes exactly @Joseph_B

I would embed, but I am unable to use that feature recently.

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.

My apologies, I misunderstood the initial question. Here’s another writeup that I think addresses your request more directly:

1 Like

Dear Shishir,

Thanks for your valuable and detailed input.
It’s a quiet common scenario and I am sure many users will benefit :handshake:

Thanks heaps @shishir… that helped heaps!
I seriously had no idea how to do it!
Thanks again!

Dear @preeyanka ,

I suggest that the above example, created by Shishir, is a good one to be added with the templates :large_blue_diamond::large_blue_diamond::large_blue_diamond:

For those interested in Shishir’s
Approach 2: Using Formulas to Create Reverse Lookups and Rollup Summaries


Please click through to :point_down:
(2021-04-01)