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
Help Article Resources
Check out the Formula Handbook template!