Tracking project's resources with formulas


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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!