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

AppSheet AppSheet Q&A
1

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

2

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

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.

4

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

1 Like
5

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

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