Need help with how to compare one row value in a column against all other values within the column

Justin326326

Hi, I am just trying to set up a managing sheet that will feed into reports > dashboards to just help team members track down work allocation in a manner where if they are overallocated, it would indicate the tasks contributing to that which would have to be managed, such as reallocating them etc.

I have it working in a sense, however I'd like to improve how I calculate my values

So, what I want is to have a column where if other tasks' Target Start - Target End overlap with other tasks that these are identified.

What I would need is for row 5 for example to compare it's dates against all other rows, if it finds ones that overlap it sums up the number of days other tasks overlap. So if one row overlapped across 2 days and another 5 days, I would have 7 days in which row 5 is being overlapped.

However, I am unable to do this as at the moment, and was just wondering if anyone could help me find a formula or solution to this

kowal

hi @Justin326326,

you can create extra column (in my case it was a checkbox) and you can use this formula inside:

=IF(COUNTIF([Start Date]: [Start Date], = [Start Date]@row) > 1, 1, 0) - if there will be two tasks with the same start date it will mark the checkbox as one. You can rebuild this formula to use also end date, assigned to etc.

hope it helps

Justin326326

https://community.smartsheet.com/discussion/comment/480040#Comment_480040

Hi @kowal thanks for the help,

In my case I am not finding where start dates are the same but where timeframes for tasks overlap

So, for example taskA runs from 01-01-25 to 23-01-25

taskB runs from 16-01-25 to 30-01-25

The number of days they would overlap would be 8 days, I would then like this same check to be done between the task in question so in this case taskA and every other row. where the number of days in which they overlap would be summed up.

This is so if a task lasted 3 days working hours would be 24 hours but the task would take 18 hours, if another task overlapped it for 1 day and lasted 6 hours that would mean 100% working hours usage which would be fine.

However, if another task overlapped 2 days that would mean with a 24 hour window there would be 30 hours of workload which be unfeasable to complete in time

I have something that does this within a month and a week's timeframe however its not exactly the best as I may be fine within a week with a 40 hour window. But for a task that runs for 3 days it may be impossible to be completed in that timeframe

kowal

aaaah ok got your point but it's bit more complicated to calculate overlapping smartsheet only recognize dates as "numbers" not something between them start and end.

but maybe I am wrong :)

Justin326326

@kowal

Not an overlapping one but I am able to calculate hours a task will take within the next working week or month or customised date range with a formula like this

=IFERROR(IF(AND([Target Start Date]@row >= TODAY(), [Target End Date]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))), NETWORKDAYS([Target Start Date]@row, [Target End Date]@row) * [Hours Per Day]@row, IF(AND([Target Start Date]@row <= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY())), [Target End Date]@row > DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))), IF([Target Start Date]@row > TODAY(), NETWORKDAYS([Target Start Date]@row, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))) * [Hours Per Day]@row, NETWORKDAYS(TODAY(), DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY()))) * [Hours Per Day]@row), IF(AND([Target Start Date]@row <= TODAY(), [Target End Date]@row >= TODAY()), NETWORKDAYS(TODAY(), MIN([Target End Date]@row, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY())))) * [Hours Per Day]@row, 0))), "Invalid Data Type")

I then source it to a collation sheet which collates everyone's workload to then determine if they have more assigned work in hours vs the hours they work in the timeframe and if so they are assigned as overallocated and the relevant tasks that contribute to that, that exist in the timeframe are highlighted so they can be reallocated.

Only issue is the values may not always be the most reliable as I mentioned before that within a 1 week timeframe you may not be overallocated but tasks can have strict timeframes within a small window and if other tasks clash then it could be impossible to complete.

I've added in a method where people can choose their own Timeframe Start and Timeframe End and for this to generate its own report, it is manual which is not the best but it can work.

I would however, prefer if it was automated where I could compare the timeframes of each task with other tasks to understand how many days other tasks overlap so I can calculate if the task I am looking at is overallocated or not.