Finding Two Submissions that Overlap With Start and End Dates and Start and End Times

[djbginns]

Hi, could someone help me fix an iFS condition that checks for a conflict of exact dates and times.do i need to combine the two to make it match? 

IFS( OR( ISBLANK([Event Start Date]), ISBLANK([Event End Date]), ISBLANK([Event Start Time]), ISBLANK([Event End Time]) ), "Conflict",

OR( // Check for exact date and time conflicts

AND( [Event Start Date] = [_THISROW].[Event Start Date], [Event End Date] = [_THISROW].[Event End Date], [Event Start Time] = [_THISROW].[Event Start Time], [Event End Time] = [_THISROW].[Event End Time] ),

// Check for overlapping date conflicts AND( [Event Start Date] < [_THISROW].[Event End Date], [Event End Date] > [_THISROW].[Event Start Date], OR( ISBLANK([_THISROW].[Event Start Time]), ([Event Start Time] < [_THISROW].[Event Start Time]) ) ) ),

"Conflict",

"No Conflict" )

[Kirk_Masden]

I'm a little confused about what you are trying to accomplish but I'll take a crack at it.  I might try to combine the separate date and time values in virtual columns to simplify the calculation.

ifs(
	or(expressions testing for empty values), "Values missing",
	and(
		[Event Start DateTime] >=  [_THISROW].[Event Start DateTime],
		[Event End DateTime] <=  [_THISROW].[Event End DateTime]
		),"Overlap",
	TRUE,"No overlap")

[Kirk_Masden]

Actually, though, I don't think with will work because I can't imagine what the distinction between the _THISROW values and the non-_THISROW values here.

[djbginns]

I got this expression to work for the start and end dates conflicting, but not the times. I also don't think a count is the best means of finding the solution. 

IF(
OR(
ISBLANK([Event Start Date]),
ISBLANK([Event End Date]),
ISBLANK([Event Start Time]),
ISBLANK([Event End Time])
),
"Conflict",
IF(
COUNT(
FILTER(
"Form Responses 1",
AND(
[Event Start Date] = [_THISROW].[Event Start Date],
[Event End Date] = [_THISROW].[Event End Date],
OR(
AND(
[Event Start Time] < [_THISROW].[Event End Time],
[Event End Time] <= [_THISROW].[Event End Time]
),
AND(
[_THISROW].[Event Start Time] < [Event End Time],
[_THISROW].[Event End Time] <= [Event End Time]
),
AND(
[Event Start Time] >= [_THISROW].[Event Start Time],
[Event End Time] <= [_THISROW].[Event End Time]
)
)
)
)
) > 1,
"Conflict",
"No Conflict"
)
)

[orimarjr69]

---

@djbginns wrote:

I got this expression to work for the start and end dates conflicting, but not the times. I also don't think a count is the best means of finding the solution. 

This worked for me thanks to your post 

IFS(
  OR(
    ISBLANK([Start DateTime]),
    ISBLANK([End DateTime])
  ),
  "Conflict",
  COUNT(
    FILTER(
      "Bookings",
      AND(
        [Start DateTime] < [_THISROW].[End DateTime],
        [_THISROW].[Start DateTime] < [End DateTime]
      )
    )
  ) > 1,
  "Conflict",
  TRUE,
  "No Conflict"
)

[djbginns · Accepted solution]

I figured it out, combined the rows as a CONCATENATE and ran this formula. 

IF(
OR(
ISBLANK([Event Start Date and Time]),
ISBLANK([Event End Date and Time])
),
"Invalid Submission",
IF(
ISNOTBLANK(
FILTER(
"Form Responses 1",
AND(
[Event Start Date and Time] < [_THISROW].[Event End Date and Time],
[Event End Date and Time] > [_THISROW].[Event Start Date and Time],
[Timestamp] <> [_THISROW].[Timestamp]
)
)
),
"Conflict",
"No Conflict"
)
)