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 |
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
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
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
Perfect Tnx 😉
