How to signal out an overlap among date ranges

user_16155193 0
Viewed 43

Have no idea how to solve the following problem:

In a given sheet where column "A" is the starting date (month/day/year), "B" is the ending date and "C" is the description, any date range overlap among rows should be signaled out in column "D".

The resulting table should look like this:

# A B C D
1 01/01/2022 02/01/2022 Task1 Row3
2 01/15/2022 02/15/2022 Task2
3 01/29/2022 02/03/2022 Task1 Row1, Row3
4 01/18/2022 02/22/2022 Task3
5 02/02/2022 02/15/2022 Task1 Row3

Link to the sheet

google-sheets-formula google-sheets
1 Answers

If the dates cross over IF(($A1<=$B$1:$B$5)*($B1>=$A$1:$A$5)

and if the Task is the same ($C1=$C$1:$C$5)

but not if the row is the same IF(ROW($A$1:$A$5)=ROW(),""

then return the Row # "Row"&ROW($A$1:$A$5)

Complete formula:

=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(($A1<=$B$1:$B$5)*($B1>=$A$1:$A$5)*($C1=$C$1:$C$5),IF(ROW($A$1:$A$5)=ROW(),"","Row"&ROW($A$1:$A$5)),"")))
user_146782670
Related