Highlight/Calculate Equipment use clashes in a project plan
Hello Community!
In the following Project Plan (example), I want to use a column formula or conditional formatting to highlight/calculate when a specific equipment that is used for a task (column named Equipment - currently set to text) is already booked for other tasks/projects at the same period. Ideally, to also return the clashing dates. I know that I can filter this information (equipment/project/dates) in a report or in card/calendar views, but when the equipment and task lists are big, this is not efficient because the user has to go through all the lines and manually check for clashes. Resource Management could do that, but we don't need this additional functionality for anything else at the moment, so the investment cannot be justified.
In the real world, the various Projects will be in different sheets, so any solution will be applied (I guess) to a report level.
I don't know if assigning a dummy email could help, without having the Resource Management feature. In Microsoft 365/Outlook you can create equipment emails and use that to "book" them (as we do for meeting rooms), but I don't know if that helps and if those emails could be added as users in smartsheet.
Many thanks in advance for your help.
Answers
-
Are you basically looking for something to flag date overlap based on the equipment?
-
Hi Paul. I want something to tell me (at row level) that equipment X that is allocated for that specific task, is overlapping to another task for the assigned dates (and this overlapping can be from projects at another sheets).
-
Having it on other sheets adds a layer of complexity but can still be managed depending on how many sheets you have.
Basically we use a COUNTIFS to see how many rows have that equipment with overlapped dates, replicate that COUNTIFS for each of the other sheets, add them all together, then wrap it in an IF to say that if there is more than one row, flag it.
=COUNTIFS(Equipment:Equipment, @cell = Equipment@row, Start:Start, @cell<= Finish@row, Finish:Finish, @cell>= Start@row)
=COUNTIFS(Equipment:Equipment, @cell = Equipment@row, Start:Start, @cell<= Finish@row, Finish:Finish, @cell>= Start@row) + COUNTIFS({Sheet 2 Equipment}, @cell = Equipment@row, {Sheet 2 Start}, @cell<= Finish@row, {Sheet 2 Finish}, @cell>= Start@row) + COUNTIFS({Sheet 3 Equipment}, @cell = Equipment@row, {Sheet 3 Start}, @cell<= Finish@row, {Sheet 3 Finish}, @cell>= Start@row)
=IF(COUNTIFS(..........) + COUNTIFS(..........) + COUNTIFS(..........)> 1, 1)
-
Thanks. I am receiving the flag correctly (tried that on a sheet only base), but in order to convert it to Column formula I need to exclude the empty cells. Trying to implement to the above formula the ISEMPTY() function but with no success.
Moreover, would this formula work if Equipment column had multiple values?
-
To exclude empties:
=IF(Equipment@row <> "", IF(COUNTIFS(..........) + COUNTIFS(..........) + COUNTIFS(..........)> 1, 1))
To incorporate multiple values, you would need to use either the HAS or CONTAINS function (depending on the equipment column type).
=COUNTIFS(Equipment:Equipment, HAS(@cell, Equipment@row), Start:Start, @cell<= Finish@row, Finish:Finish, @cell>= Start@row)
-
Once more thanks for your time. I am working on one worksheet level at this phase.
I use the following formula based on what I understood from your instructions, but I get the red flag for the empty parent cells as well not the other way around.
=IF(Equipment@row <> "", COUNTIFS(Equipment:Equipment, @cell = Equipment@row, Start:Start, @cell <= Finish@row, Finish:Finish, @cell >= Start@row) > 1, 1)
-
(Edited because I found the reason that the HAS is not working)
I have added a drop down column and used the HAS formula and it seems that it doesn't work correctly when I use multiple values on a cell. So, in this case CONTAINS is the correct function to use.
-
The issue with the parent rows being flagged is because your syntax is just a little off.
IF(Equipment@row <> "", IF(COUNTIFS(..........) + COUNTIFS(..........) + COUNTIFS(..........)> 1, 1))
-
Hello Paul.
Can you please help me correct the formula but at a worksheet level (the countifs(...) part) which is a little off as you say?
Moreover, I have created a column to see how many clashes exist, using the dropdown column (Equipment2). However, it seems that the formula can't work because the Contains() formula has to look into an array column (since the column contains multiple values in some cases). So I don't know if this is feasible or not (and this is a general question regarding the dropdown columns with multiple values and if it is possible to use them in the search criteria of any function).
=COUNTIFS([Equipment2]:[Equipment2], CONTAINS(@cell, [Equipment2]3), Start:Start, @cell <= Finish@row, Finish:Finish, @cell >= Start@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
