How do I flag a scheduling conflict for affected rows only on complex sheet?

AdamB

Hello SmartSheet Gurus,

I run a greenhouse horticulture company and I am trying to figure out a way to flag individual rows that have labour scheduling conflicts with other rows. I have been pouring over the various threads on here that relate to scheduling conflicts, but nothing addresses my unique situation.

As the solution I am looking for is quite complicated (for me, anyway), I will lay out what I am looking for in two parts.

Part 1:

Right now, on the example below, in the Conflicts column, I am using the formula =IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIFS([End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row) - 1 > 0, "CONFLICT"))

This lets me know that there are indeed conflicts on the page, but I would really like for it to only flag rows that specifically have conflicts with other rows. In my screenshot below, I have highlighted Rows 11 and 53, which conflict directly with each other. I can verify that other rows as shown on this screenshot do not conflict with any other events, but still get the "CONFLICT" message from that formula.

Part 2:

To add a little more complexity to the solution that I am looking for, when I unhide all the rows, you can see that there are activities that do not include labour. All labour events are marked by a letter (H, in this example, but there are other letters used) in the LABOUR EVENT column. Non-labour activities are left blank in the LABOUR EVENT column.

With all of the above in mind, is it possible to mark labour conflicts only, per the rows that have the actual labour conflicts occurring in them and if so, how? It would really me out a great deal to quickly see which rows have conflicts occurring in them.

Paul Newcome

You would need to reference [Labor Event]@row (see three bold portions)

=IF([LABOUR EVENT]@row <> "", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIFS([LABOUR EVENT]:[LABOUR EVENT], @cell = [Labor Event]@row, [End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row) - 1 > 0, [Labor Event]@row + " Conflict")))

Paul Newcome

To have it applied to only labor events, start your formula out with another IF saying as much.

=IF([Labor Event]@row <> "", existing_formula)

You are also going to want to add this range/criteria set to your COUNTIFS so that it is only looking at labor events as well.

[Labor Event]:[Labor Event], @cell <> ""

Lets see if accounting for labor events is enough to clear up those rows that shouldn't be flagged.

AdamB

Thanks for this input, @Paul Newcome. Now that I have added those conditions (hopefully correctly), the CONFLICTS cells are all coming up blank. The good news is that I am not getting error messages, the bad news is that it is not flagging individual conflicts. I think there is a strong change I have not interpreted your instructions properly - perhaps the @cell below is the issue? Here is what I am using now:

=IF([LABOUR EVENT]@row <> "H", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIFS([End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row) - 1 > 0, [LABOUR EVENT]:[LABOUR EVENT], @cell <> "", "CONFLICT")))

Paul Newcome

The range/criteria set should be added as a range/criteria set within the COUNTIFS. It also looks like you need to adjust your closing parenthesis to close out the OR function before moving on to the output.

AdamB

OK, thanks for that input. Your solution works to show if any labour event conflicted with another, which is great. Now, I want to get more specific and flag only when one department's labour conflicts with itself. I have designated each department's labour events with specific letters: H, C, and P for now, in the LABOUR EVENT column.

One solution I found to accomplish this is below, but I have had to make three separate columns to flag each department's conflicts. Here is the solution for conflicts in the "H" department only:

=IF([LABOUR EVENT]@row = "H", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIFS([LABOUR EVENT]:[LABOUR EVENT], @cell = "H", [End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row) - 1 > 0, "H Conflict")))

If I wanted this to flag these labour inter-department conflicts, but keep it in only one column, I am assuming I would want somehow to build in an AND or OR function. Does that sound about right to anyone? I have taken a few cracks at it so far, but haven't been successful yet.

Always appreciate any feedback or ideas!

Paul Newcome

You would need to reference [Labor Event]@row (see three bold portions)

=IF([LABOUR EVENT]@row <> "", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIFS([LABOUR EVENT]:[LABOUR EVENT], @cell = [Labor Event]@row, [End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row) - 1 > 0, [Labor Event]@row + " Conflict")))

AdamB

Thank-you, @Paul Newcome. Your insight here has been of tremendous value and is greatly appreciated.

Paul Newcome

Happy to help. 👍️