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

Find more posts tagged with

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

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")

Tapio Pulkkinen

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

https://community.smartsheet.com/discussion/comment/295553#Comment_295553

Paul Newcome

What is the maximum number of workers you can have in a single cell?

Tapio Pulkkinen

https://community.smartsheet.com/discussion/comment/295675#Comment_295675

At this moment, maximum number of workers per project or per single cell is 5.

Paul Newcome

Is your resource column a multi-select dropdown or a contact type column?

Tapio Pulkkinen

https://community.smartsheet.com/discussion/comment/295681#Comment_295681

It is a multi-select dropdown and I would prefer to keep it like that if it is possible.

Paul Newcome

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:

Then your criteria for the COUNTIFS will be:

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.

Tapio Pulkkinen

https://community.smartsheet.com/discussion/comment/295693#Comment_295693

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.

Paul Newcome

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 $.

Tapio Pulkkinen

https://community.smartsheet.com/discussion/comment/295814#Comment_295814

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.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.