Tracking project's resources with formulas
Hello,
My problem is that I’m trying to create a formula that would check if one worker (resource) has more than one project at the same time.
I have used following formulas:
Formula that checks if projects overlap:
=IF(COUNTIFS([Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "Yes"; "No")
Formula that checks if worker has one or more projects at the same time:
=IF(COUNTIFS(Resource:Resource; Resource@row; [Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "2 or more"; "No")
Formula that checks if projects are overlapping each other works fine but the second formula works only if ‘Resource’ column has exact same workers. For example, in the 'Example' picture it can been seen that workers 1 and 5 has two overlapping projects but only worker 5 is found out by the formula. For information, resource-column is a multi-select dropdown list and every formula should be ‘Column formula’ because projects are added and removed weekly.
I’ve tried HAS(), CONTAINS() and FIND() functions but only way I can get the formula to work is to create a column for every worker. There should also be option to add and remove workers so one column for each worker wouldn’t solve the problem. Is there some sort of way to get this work or is this impossible?
Thanks for advance
Best Answer
-
Ok. What we will need to do is insert 5 columns to the right of the multi-select. These can later be locked/hidden after setting everything up. Label these column A through E.
In column A enter this:
=IF(COUNTM(Resource@row) = 1; Resource@row; LEFT(Resource@row; FIND(CHAR(10); Resource@row) - 1))
Then enter this into column B and dragfill over to column E then on down the rows:
=LEFT(SUBSTITUTE($Resource@row + CHAR(10); JOIN(COLLECT($A@row:A@row; $A@row:A@row; @cell <> ""); CHAR(10)) + CHAR(10); ""); FIND(CHAR(10); SUBSTITUTE($Resource@row + CHAR(10); JOIN(COLLECT($A@row:A@row; $A@row:A@row; @cell <> ""); CHAR(10)) + CHAR(10); "")) - 1)
Then your criteria for the COUNTIFS will be:
=IF(COUNTIFS(Resource:Resource; OR(HAS(@cell; A@row); HAS(@cell; B@row); HAS(@cell; C@row); HAS(@cell; D@row); HAS(@cell; E@row)); [Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "2 or more"; "No")
NOTE: If you get any errors initially with any of the formulas, please first check to make sure I didn't miss swapping any commas out for semi-colons.
Answers
-
Try this...
=IF(COUNTIFS(Resource:Resource; HAS(@cell; Resource@row); [Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "2 or more"; "No")
-
Thank you for the quick reply!
That formula of yours doesn't work in all cases. It should point out if any worker in that row has more than one project at once. For example, in the upper picture, rows 1, 2, 5 and 6 should be marked as "2 or more". In the picture below, rows 1, 2 and 3 should also be marked as "2 or more".
I hope you understand my problem and are able to help. Thanks
-
What is the maximum number of workers you can have in a single cell?
-
At this moment, maximum number of workers per project or per single cell is 5.
-
Is your resource column a multi-select dropdown or a contact type column?
-
It is a multi-select dropdown and I would prefer to keep it like that if it is possible.
-
Ok. What we will need to do is insert 5 columns to the right of the multi-select. These can later be locked/hidden after setting everything up. Label these column A through E.
In column A enter this:
=IF(COUNTM(Resource@row) = 1; Resource@row; LEFT(Resource@row; FIND(CHAR(10); Resource@row) - 1))
Then enter this into column B and dragfill over to column E then on down the rows:
=LEFT(SUBSTITUTE($Resource@row + CHAR(10); JOIN(COLLECT($A@row:A@row; $A@row:A@row; @cell <> ""); CHAR(10)) + CHAR(10); ""); FIND(CHAR(10); SUBSTITUTE($Resource@row + CHAR(10); JOIN(COLLECT($A@row:A@row; $A@row:A@row; @cell <> ""); CHAR(10)) + CHAR(10); "")) - 1)
Then your criteria for the COUNTIFS will be:
=IF(COUNTIFS(Resource:Resource; OR(HAS(@cell; A@row); HAS(@cell; B@row); HAS(@cell; C@row); HAS(@cell; D@row); HAS(@cell; E@row)); [Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "2 or more"; "No")
NOTE: If you get any errors initially with any of the formulas, please first check to make sure I didn't miss swapping any commas out for semi-colons.
-
Thank you a lot! That setup works perfectly!
But, can that formula, what goes to columns B - E, be changed so it could be 'column formula'? It seemed to work without '$'-symbol but it still couldn't be changed to 'column formula'. I can work with these formulas as they are now so if it can't be changed I'm more than fine with it.
Again, Thanks. It would have taken probably weeks to figure this out without any help.
-
I would suggest leaving the $, dragfill over so that column/cell references adjust as they should, then go into each of the columns and remove the $.
-
Got it now, thanks. I don't know what I did wrong at first try.
For the future: Can these formulas be modified so that I would be able to see if there are 2 projects or 3 projects at the same time for one worker? If can, how it would be done? It isn't necessary but if it is easy, I would like to know.
-
These last formulas are for parsing out the Multi-select column. The formula where we used the IF/COUNTIFS is what is going to determine whether they have one or more than one.
=IF(COUNTIFS(Resource:Resource; OR(HAS(@cell; A@row); HAS(@cell; B@row); HAS(@cell; C@row); HAS(@cell; D@row); HAS(@cell; E@row)); [Finish date]:[Finish date]; >[Start date]@row; [Start date]:[Start date]; <[Finish date]@row) > 1; "2 or more"; "No")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
