I have a question in regards to a resource management planner I am building in Coda. In the table I list all the tasks which have a start date an end date and an effort column. Using these three variables my aim is to know how much is the total effort for each of my staff members and then apply conditional formatting on it so for example if an employee has a workload of 100% in Project 1 and 50% in Project 2, then the sum is 150% and this means “RED” because they are overloaded with work.
I have managed to come very close to solving this problem by building a formula that does this: If dates overlap sum the efforts, if they dont overlap do not sum the efforts. The ultimate goal with this is to have this viewed in a gantchart whereby I can move task dates around and the conditional formatting (i.e the sum of total effort) changes dynamically. The problem i get with the formula i placed is that even when dates dont overlap one of the tasks still gets the total sum of effort which should not be the case, any ideas how to solve this ?
I have used the formula below to achieve this:
Tasks.Filter(thisRow.[Start Date]>=[Start Date]AND [Start Date]<=[End Date]ORthisRow.[End Date]>= [Start Date]AND thisRow.[End Date]<= [End Date] OR thisRow.[Start Date]<= [Start Date] AND thisRow.[End Date]>= [End Date]).Effort.sum()
There is a link in Reddit with a video, where I explain the issue: Click Link for Post in Reddit
Otherwise here is the page: