Here is a tough one - Identify Route Blackouts

tgattsh

I need to schedule work on a particular route. Within my records I have a column named "Route" that shows the route number. EX: 10402. I have a DATE column called "Target". I want to add a column called "B/O Check" and write a formula that will take my target date and route number, look that data up in a sheet called "MW Route Blackouts" and return if that date is within a blackout or not and also how many days are left in the blackout. EX: "BO +7" or "Good +4" (if not in blackout). Show if I went to my sheet today and selected Monday 6/3 as my "Target" for a record that is on route 10402. My [B/O Check]@row would return "BO +10". Telling me my target date will be in blackout on that day and for an additional 10 days after my target date.

This is probably pretty easy for some of you Rockstars…

Paul Newcome

The INVALID COLUMN VALUE error means that you are trying to put a non-date value into a date type column, but after looking at the formulas I gave you for testing, I realized that the larger one I gave you didn't actually account for the [Area-Route]. Try this in the BO Check column:

=IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, "Good", "BO") + " +" + (MIN(COLLECT(IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), @cell >= [Target Date]@row, {Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row)) - [Target Date]@row)




Paul Newcome

You would need to restructure your reference sheet so that each route and each set of dates is on its own row and separate columns for start and finish dates.

tgattsh

@Paul Newcome Do you mean something like this? (BB-Blackout Begin, BE-Blackout End)

Paul Newcome

I mean more like this:

tgattsh

@Paul Newcome I hope you had a great weekend Sir…
I'm not sure what I am asking for is even possible but here it goes. I have this sheet called "Workorder",

I have this sheet "Route Blackouts V1" with the blackout dates laid out as you described earlier,

In my Workorder sheet I have a column called "BO Check" and would like to write a formula that will take my target date and area-route number, look that data up in a sheet called "Route Blackouts V1" and return if that date is within a blackout or not and also how many days are left in the blackout. EX: "BO +7" or "Good +4" (if not in blackout).

So with the data currently shown, record 1 of the Workorder sheet with a target date of 6/24 would return a value of "BO +14" meaning this records target date falls in a blackout and will be for the following 14 days of the target date.

Record 2 of the Workorder sheet with a target date of 7/8 would return a value of "Good +7" meaning this records target date falls outside a blackout and will be for the following 7 days.

Any assistance you could provide would be exceedingly appreciative. Thank you…

Paul Newcome

Now that you have adjusted the Blackout Reference Table, we can use a formula such as this:

=IF(COUNTIFS({Reference Table Area-Route}, @cell = [Area-Route]@row, {Reference Table Start}, @cell <= [Target Date]@row, {Reference Table End}, @cell >= [Target Date]@row) = 0, "Good", "BO") + " +" + (MIN(COLLECT(IF(COUNTIFS({Reference Table Area-Route}, @cell = [Area-Route]@row, {Reference Table Start}, @cell <= [Target Date]@row, {Reference Table End}, @cell >= [Target Date]@row) = 0, {Reference Table Start}, {Reference Table End}), IF(COUNTIFS({Reference Table Area-Route}, @cell = [Area-Route]@row, {Reference Table Start}, @cell <= [Target Date]@row, {Reference Table End}, @cell >= [Target Date]@row) = 0, {Reference Table Start}, {Reference Table End}), @cell >= [Target Date]@row)) - [Target Date]@row)

tgattsh

@Paul Newcome Hi Paul. I feel out of my chair after I saw your formula, made the references and it seem to work right off the bat. I added a column to check the number of days by hand to check each one and here is what I got.

It seemed record 1 and 3 calculated correctly. some are just a day off and others are way off. The Good or Bad seems to work just fine. I checked the line 7 and ran through each day in the target date started at 6/20 showed Good +4 as it shows above then began adding day after day to see what it showed. 6/21 +3, 6/22+2, 6/23+1, 6/24 +0, 6/25 +20. Any ideas?

tgattsh

The formula,

=IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, "Good", "BO") + " +" + (MIN(COLLECT(IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), @cell >= [Target Date]@row)) - [Target Date]@row)

Paul Newcome

Let's pick one that is way off and see if we can dig into it a bit. Can you provide a screenshot of the reference sheet filtered to only show the BO dates for "100-265"?

tgattsh

@Paul Newcome

Paul Newcome

Let's try inserting two temporary date columns called "Min Start" and "Min End" and then use these two formulas (column formulas but still working on this "100-265" entry).

Min Start:

=MIN(COLLECT({Route Blackouts V1 Start}, {Route Blackouts V1 Start}, @cell >= [Target Date]@row))

Min End:

=MIN(COLLECT({Route Blackouts V1 End}, {Route Blackouts V1 End}, @cell >= [Target Date]@row))

tgattsh

I changed reference for {Route Blackouts V1 Start} to only show 100-265 and I get a INVALID COLUMN VALUE.

tgattsh

@Paul Newcome

Paul Newcome

The INVALID COLUMN VALUE error means that you are trying to put a non-date value into a date type column, but after looking at the formulas I gave you for testing, I realized that the larger one I gave you didn't actually account for the [Area-Route]. Try this in the BO Check column:

=IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, "Good", "BO") + " +" + (MIN(COLLECT(IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), IF(COUNTIFS({Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row, {Route Blackouts V1 Start}, @cell <= [Target Date]@row, {Route Blackouts V1 End}, @cell >= [Target Date]@row) = 0, {Route Blackouts V1 Start}, {Route Blackouts V1 End}), @cell >= [Target Date]@row, {Route Blackouts V1 Area-Route}, @cell = [Area-Route]@row)) - [Target Date]@row)




tgattsh

@Paul Newcome

That did the trick!!!

Paul Newcome

Happy to help. 👍️