Valid if help

[Rob_Zehentner]

I have a table with date columns. One column has a valid if statement to prevent adding two events at the same time. It works great, except, I can’t edit entry. It says my dates are invalid since I’m editing an existing line.

Does anyone know a workaround for this?

[Aleksi]

What is your existing formula?

[Rob_Zehentner]

AND([Start Date] <= [End Date],
COUNT(
FILTER(“Rental”,
AND(
[Inflatable] = [_THISROW].[Inflatable],
AND([Start Date] >= [_THISROW].[Start Date],[Start Date] <= [_THISROW].[End Date])

   				 )
	  )
) 

= 0)

[Steve]

Your FILTER() expression needs to omit the current row. The most efficient way is to remove the current row from the results. For instance:

AND(
  ([Start Date] <= [End Date]),
  ISBLANK(
    FILTER(
      "Rental",
      AND(
        ([Inflatable] = [_THISROW].[Inflatable]),
        ([Start Date] >= [_THISROW].[Start Date]),
        ([Start Date] <= [_THISROW].[End Date])
      )
    )
    - LIST([_THISROW])
  )
)

Note FILTER(...) - LIST([_THISROW]), which removes the current row from the results.

Note, too, that I simplified your AND() expression within the FILTER(), and replaced COUNT(...) = 0 with ISBLANK(...), which has the same effect but is shorter.

[Rob_Zehentner]

Thanks so much! I know it was simple, but slightly above my paygrade.