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.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!