

Active Member

December 11, 2022

HI,
please can somebody help me with formula.
I would like to filter by one more criteria: meeting room and dates for the same column to check overlap of time ?
([@[Start Time]]<FILTER([End Time];[Meeting Room]=[@[Meeting Room]]))*
([@[End Time]]>FILTER([Start Time];[Meeting Room]=[@[Meeting Room]]))
)>1
Dates | Meeting Room | Start Time | End Time | Overlap |
11.pro | A | 7:30 | 8:30 | FALSE |
10.pro | B | 8:00 | 9:00 | FALSE |
9.pro | B | 10:00 | 10:30 | TRUE |
14.pro | A | 8:30 | 9:00 | FALSE |
10.pro | B | 10:15 | 12:00 | TRUE |
9.pro | A | 9:30 | 11:00 | TRUE |
11.pro | A | 10:30 | 11:30 | TRUE |


July 16, 2010

Hi Dejan,
Please see this post on identifying overlapping dates and times in Excel.
If you're still stuck, come back and share your file so we can see the problem.
Mynda


VIP

Trusted Members

December 7, 2016

Hello,
Just add the date with the time.
=SUMPRODUCT(
([@[Dates]]+[@[Start Time]]<FILTER([Dates]+[End Time];[Meeting Room]=[@[Meeting Room]]))*
([@[Dates]]+[@[End Time]]>FILTER([Dates]+[Start Time];[Meeting Room]=[@[Meeting Room]]))
)>1
But in your example file the dates column is not part of the table, you need to fix that first.
Br,
Anders
1 Guest(s)
