How to use any date within a date range as formula criteria?
![greg.hobbs](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
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)
-
Awesome! Thanks for posting your solution 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!