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

greg.hobbs
greg.hobbs ✭✭
edited 01/30/23 in Formulas and Functions

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!

Answers

  • greg.hobbs
    greg.hobbs ✭✭
    edited 01/30/23

    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.
    Genevieve P. Employee Admin

    Awesome! Thanks for posting your solution 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!