Workload of staff on overlapping dates

Hi Friends,

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:

Hey there!

it looks like this doc was taken from a gallery template - which is great!

But because of that, I can’t quite locate the issue/formula inside your doc. Can you give more pointers and information as to where this formula/table is?

I apologize Scott, I am new to coda so I accidentally attached the whole doc.
I think I have fixed it now, the doc is now attached but I dont know why it says “View Only”

I have also published this question with a video in “Reddit” if you want to watch it https://www.reddit.com/r/codaio/comments/12bbrsh/how_to_do_workload_of_staff_in_overlapping_dates/?utm_source=share&utm_medium=web2x&context=3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.