Dejan Kumpar
Active Member
Members
Forum Posts: 5
Member Since:
December 11, 2022
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 ?
=SUMPRODUCT(
([@[Start Time]]<FILTER([End Time];[Meeting Room]=[@[Meeting Room]]))*
([@[End Time]]>FILTER([Start Time];[Meeting Room]=[@[Meeting Room]]))
)>1
([@[Start Time]]<FILTER([End Time];[Meeting Room]=[@[Meeting Room]]))*
([@[End Time]]>FILTER([Start Time];[Meeting Room]=[@[Meeting Room]]))
)>1
Thank you in adv.
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 |
Mynda Treacy
Admin
Forum Posts: 4696
Member Since:
July 16, 2010
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
Anders Sehlstedt
Eskilstuna, Sweden
VIP
Members
Trusted Members
Trusted Members
Forum Posts: 899
Member Since:
December 7, 2016
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
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Dirk Veeken, Stephanie Phillips, Jorge Chicas
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
Catalin Bombea: 1909
SunnyKow: 1432
Anders Sehlstedt: 899
Purfleet: 414
Frans Visser: 346
David_Ng: 306
Hans Hallebeek: 279
lea cohen: 245
Jessica Stewart: 219
A.Maurizio: 213
Newest Members:
Sheila Sanders
Francine Walshe
Sean Ladlow
Jorge Chicas
mahmoud salem
Irvine Choolo
Deon Bouwer
Amgad Squires
jim bo
Linda Bock
Forum Stats:
Groups: 3
Forums: 24
Topics: 6836
Posts: 29985
Member Stats:
Guest Posters: 49
Members: 33568
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —