How to use any date within a date range as formula criteria?

greg.hobbs

O, great wizards of the Smartsheet community, hoping you can help me cast a formula spell.

Trying to create a formula to flag if a resource is overallocated in a master sheet that tracks resources across multiple projects. NOT using either the legacy resource management or RM by SS, just core Sheet functionality.

I managed to solve for overallocation on a single date, with:

=IF(SUMIFS([% Allocation]:[% Allocation], Resource:Resource, =Resource@row, Start:Start, =Start@row) > 1, 1, 0)

Which in practice does:

=if the sum of all [% Allocation] for the [Resource] and [Date] on the current row is over 100%, check the box.

Now I know this person is overallocated on this date, and can use conditional formatting to highlight areas that need review on the Gantt chart.

But, what I want is to capture if ANY dates within the current row's [Start] and [End] range overlap with any other row on the sheet (and sum the allocation). I don't need to return which specific dates in the range are an issue, just identify that these date ranges need to be reviewed with the checkbox.

ex: both of the below rows should be checked, because from Feb 14-28 Steve is 150% allocated.

Row 1: Steve is 75% allocated from 2023.02.01 - 2023.02.28

Row 25: Steve is 75% allocated from 2023.02.14 - 2023.03.15

Let me know if you have any ideas!

greg.hobbs

I figured it out!

In case anyone is here from googling looking a similar function, here is what worked. This formula is on the flag column, and cell will be flagged if there is over 100% allocation for a resource within any dates that overlap on the sheet.

=IF(SUMIFS([% Allocation]:[% Allocation], Resource:Resource, Resource@row, End:End, >=Start@row, Start:Start, <=End@row) > 1, 1, 0), 0)

Genevieve P.

Awesome! Thanks for posting your solution 🙂