Flag for Overlap Meeting Time
I have created a sheet for meeting room reservation purpose with flag function if duplicate section found.
Details shown in picture "Question 1".
Question 1
May I know if there is any formula only flag the duplicate row with higher Row ID number?
Question 2
Current flag function only applicable to duplicate section(criteria is date_start time), i would like to flag it if meeting B overlap meeting A with same date but different start time. Please advise if there is any formula can solve this question.
Thanks
Joseph
Best Answer
-
I think we are up to 10 pages of comments at this point, but I do know that there is a solution in here for flagging overlap in date/time.
Answers
-
I think we are up to 10 pages of comments at this point, but I do know that there is a solution in here for flagging overlap in date/time.
-
@Paul Newcome , thanks for the sharing!
The link above I found in your post are exactly what I need but I am just curious how to convert the start & finish column to time formula? Or I need to create 2 more columns for the formula show as below?
Below is my trial sheet without any confidential information.
For start & end column are manually entered, column X&Y are using the start & finish formula given in your sheet, column 6 are using overlap formula.
Row 7,8 & 9 do have conflict but it didn't flag, may I know why?
=IF(COUNTIFS(ID:ID, ID@row, Date:Date, Date@row, Finish:Finish, @cell >= Start@row, Start:Start, @cell <= Finish@row) > 1, 1)
Hopefully you can teach me what to do so I can deploy the formula myself & teaching others just like you!
-
Because you are including the ID column as part of your criteria in the overlap search technically there is no overlap because the ID is not repeated. Try removing that from the COUNTIFS and see if it starts working as expected for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!