Flag for Overlap Meeting Time

Joseph Chiang

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.PNG

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

Paul Newcome

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.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Paul Newcome

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.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Joseph Chiang

@Paul Newcome , thanks for the sharing!

https://app.smartsheet.com/b/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd&_ga=2.135621425.1642209810.1649031528-476625743.1645663213

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!

Paul Newcome

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.