Solved: Finding Two Submissions that Overlap With Start an... - Page 2

djbginns · 11-27-2023 03:40 PM

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" )

djbginns

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"
)
)

View solution in original post

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