Log In
Register
Home
Active Member
December 11, 2022
Offline
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
Offline
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
Active Member
December 11, 2022
Offline
Hi Mynda,
still stuck, I have check tutorial but still doesn't now how to use filter with two factor check.
I would like to check overlap time over the Meeting rooms and also to check a dates?
I have attached excel ...
Tnx very much for help.
BR
Dejan
VIP
Trusted Members
December 7, 2016
Offline
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)
