How to create a flag column that identifies a scheduling conflict

JuliannaK

I am trying to create a checkbox column that gets checked when the conditions of a formula are met. Essentially, I am trying to flag a row if someone is assigned to that row who already has an assignment for that same date AND if the start and end times overlap.

Example:

Interpreter= John; Date= 6/1/2025; Start Time= 8:00 AM; End Time = 10:00 AM

Now if John is assigned to another row that has Date= 6/1/2025; Start Time=9:00 AM; End Time= 10:00 AM, then the conflict column should be checked. I also understand that both rows will be checked which is fine.

I first created column to change my start and end time to military format which I got from another community post:

=VALUE((VALUE(LEFT([Start Time]@row , FIND(":", [Start Time]@row ) - 1)) - IF(VALUE(LEFT([Start Time]@row , FIND(":", [Start Time]@row ) - 1)) = 12, 12, 0) + IF(RIGHT([Start Time]@row , 2) = "pm", 12, 0)) + MID([Start Time]@row , FIND(":", [Start Time]@row ) + 1, 2))

***(I had also tried a more simple TIME([Start Time]@row , 1, 2) but I realize that the new column I am trying to create might not work well with the semicolons)

Here is the formula I am using to indicate if there is a conflict:

=IF([Conflict Override]@row = 1, 0, IF(Interpreter@row <> "", IF(COUNTIFS(ReqID:ReqID, ReqID@row , [Start Date]:[Start Date], [Start Date]@row , [Military End Times]:[Military End Times], @cell >= [Military Start Times]@row , [Military Start Times]:[Military Start Times], @cell <= [Military End Times]@row ) > 1, 1), ""))

Now, I am not sure how to explain this next part because it doesn't make sense to me but, the above formula works for the whole sheet only when the Military Start Times and Military End Times is NOT a column formula AND is ONLY in the first row. In fact, the conflict is being checked correctly for rows where the military time columns do not have data.

Any help would be appreciated.

Paul Newcome

I didn't have a parenthesis missing. You added an extra IF function to my formula. My formula included the COUNTIFS inside of the AND.

Is that error present in even a single cell in any of the referenced columns?

Try wrapping both of the string column formulas in an IFERROR.

=IFERROR(VALUE(…………), "")

Paul Newcome

Give this a try…

Put this in a text/number column called "Start String":

=VALUE(YEAR([Start Date]@row) + RIGHT("0" + MONTH([Start Date]@row), 2) + RIGHT("0" + DAY([Start Date]@row), 2) + RIGHT("0" + LEFT([Military Start Times]@row, IF(LEN([Military Start Times]@row) = 3, 1, 2), 2) + RIGHT([Military Start Times]@row, 2))

Put this in a text/number column called "End String":

=VALUE(YEAR([End Date]@row ) + RIGHT("0" + MONTH([End Date]@row ), 2) + RIGHT("0" + DAY([End Date]@row ), 2) + RIGHT("0" + LEFT([Military End Times]@row , IF(LEN([Military End Times]@row ) = 3, 1, 2), 2) + RIGHT([Military End Times]@row , 2))

The above should output a number based on the start / end date that is in this format: YYYYMMDDhhmm

Then the formula for the flag column is:

=IF(AND([Conflict Override]@row <> 1, Interpreter@row <> "", COUNTIFS(ReqID:ReqID, @cell = ReqID@row, [Start String]:[Start String], @cell <= [End String]@row, [End String]:[End String], @cell >= [Start String]@row) > 1), 1)

JuliannaK

@Paul Newcome I still get an error with that solution. For my original conflict formula I get #INVALID VALUE and using your system I get #INVALID DATA TYPE

For context, here are the columns and formula for my way. The ReqID column is not shown, but it is just a combination of the name of the interpreter and the start date and expected end date of an assignment. If it is unassigned it will just be the start date and expected end date, but we are already taking into consideration if the interpreter field is blank for the conflict flag.

and here is the formula you suggested as well as the columns in use with a few minor tweaks to your formula as there was a bracket missing after the AND function.

Thanks for your help on this.

Paul Newcome

I didn't have a parenthesis missing. You added an extra IF function to my formula. My formula included the COUNTIFS inside of the AND.

Is that error present in even a single cell in any of the referenced columns?

Try wrapping both of the string column formulas in an IFERROR.

=IFERROR(VALUE(…………), "")

JuliannaK

@Paul Newcome I see what you mean now for the AND function and switched your formula back to the one you posted.

But actually, thank you for pointing it out, it was an error in the last row of the sheet that was preventing both my solution and yours from working. This has been driving me crazy for three days so thank you again.

Paul Newcome

Happy to help. 👍️