Alert or Count if date is the within the same date range within the same column.

Sam Swain · 05/11/23

Hello community,

I am trying to make two different sub sections (BROADCAST and MAINTENANCE) of the same column flag any overlapping dates.

In the image below there is a BROADCAST tab and a MAINTENANCE in the Task column The objective is to make sure that no maintenance is occurring at the same time as broadcasting. Row 136 and Row 187 have dates falling within the same range.

I need a formula that will return "ALERT" or COUNT when a broadcast date is overlapping with any maintenance date.

Does anybody see a possible solution for this?

Paul Newcome · 05/23/23

@Sam Swain Leave it exactly as is. "@cell" is just saying to take that previous range and evaluate it on a cell by cell basis.

Paul Newcome · 06/21/23

Our criteria is that there is date overlap and a non-match on the helper column (maintenance and broadcast with overlapping dates for example).

We start with the date overlap. The most efficient way to determine if there is any overlap is to look at the entire [End Date] column and compare it to the [Start Date]@row.

[End Date]:[End Date], @cell>= [Start Date]@row

Then do the opposite by looking at the [Start Date] column and comparing it to the [End Date]@row.

[Start Date]:[Start Date], @cell<= [End Date]@row

If you have an end date greater than or equal to my start date and a start date less than or equal to my end date, then there is some kind of overlap in dates.

[End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row

Now that we have the overlap figured out we can move on to the helper column. Our criteria for that range is that the cell is not blank and the cell does not match [Helper Column]@row. We don't care that we have two rows overlapping for maintenance. We want to flag (for example) when maintenance overlaps broadcast and are not worried about blanks.

[Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row)

Now we know the ranges we want to use and the criteria for each, so we count how many rows match all of that.

COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))

If we have 1 or more rows that meet all of those range/criteria sets

=IF(COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))> 0, "ALERT")

.

Hopefully my responses to your questions may make a little more sense now that we have the context of the formula breakdown.

Q1) We are not outputting a number into a cell, but we are counting how many rows meet the range/criteria sets and evaluating that output in the IF statement.

=IF(COUNTIFS(..............)> 0, "ALERT")

Q2) See the first section above. The short explanation is... If your End Date is greater than or equal to my Start Date and your Start Date is less than or equal to my End Date, then there is date overlap.

Q3) Helper column comes next because that is one of the range/criteria sets we are evaluating. Technically this range/criteria set could have come first within the COUNTIFS. I personally just like to stack the more complicated pieces of a formula towards the end so I don't have a bunch of closing parenthesis scattered throughout.

Q4a) The first part is explained throughout the above.

Q4b) "@cell" just tells the function to look at the previously established range and evaluate it on a cell by cell basis.

COUNTIFS([Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))

COUNT the [Helper Column]:[Helper Column] range IF the cell is not equal to blank AND the cell is not equal to [Helper Column]@row.

Paul Newcome · 05/13/23

You would need a helper column to bring the Broadcast and Maintenance values onto each row.

=IF(COUNT(CHILDREN(Task@row)) = 0, INDEX(ANCESTORS(Task@row), 2))

From there you would use something along the lines of

=IF(COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))> 0, "ALERT")

Sam Swain · 05/23/23

https://community.smartsheet.com/discussion/comment/375625#Comment_375625

Hi Paul, apologies for the delay in replying. As ever, your smartsheet knowledge seems to know no bounds!

I've done the helper column as you suggested. This is working.

I haven't figured out the second part formula which you suggested...

=IF(COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))> 0, "ALERT")

When you reference "@cell" x4 in this formula, which cell should I be referencing here?

Thanks again in advance.

Paul Newcome · 05/23/23

@Sam Swain Leave it exactly as is. "@cell" is just saying to take that previous range and evaluate it on a cell by cell basis.

Sam Swain · 05/23/23

https://community.smartsheet.com/discussion/comment/377105#Comment_377105

Okay that seems to be working pretty well! However, there seems to be many "ALERTS" where I don't see an overlap, as shown below. Alert is replaced with "⚠"

I'm not sure why Maintenance tasks with the date 01/01/23 show an overlap - there are no rows under the Broadcast heading that fall under this date (as you can see in the gantt). When I change the date to 01/31/23 the "⚠" goes away. Thoughts?

Sam Swain · 06/06/23

Worked out the answer, it was calculating the other subheadings below for bank holidays and project key dates. Once I deleted the formula from those sections the alert is working perfectly.

Thank you @Paul Newcome - you are a smartsheet genius!!!

Sam Swain · 06/21/23

@Paul Newcome - following our chat on linkedin, I wonder if you could give me and those on this thread a better understanding of how the following formula you provided works...

=IF(COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row, [Helper Column]:[Helper Column], AND(@cell <> "", @cell <> [Helper Column]@row))> 0, "ALERT")

Below are a list of questions but if it's easier to break the formula down bit by bit, maybe do it that way. Thanks again.

Q1 - why do you use COUNTIFS when there is no "counting" or numbers being returned?

Q2 - does the formula begin by saying the range we are interested in is the End Date column range? Then it is says read every cell (@cell) between all the dates in the start date column and compare it to the end date row (@cell<= [End Date]@row) ?

Q3 - I don't understand why [Helper Column]:[Helper Column] comes next

Q4 - I don't understand why AND(@cell <> "", @cell <> [Helper Column]@row))> 0, "ALERT") comes after that and I do not understand what @cell these are referring to.

Paul Newcome · 06/21/23