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?
What is your existing formula?
AND([Start Date] <= [End Date],
COUNT(
FILTER(โRentalโ,
AND(
[Inflatable] = [_THISROW].[Inflatable],
AND([Start Date] >= [_THISROW].[Start Date],[Start Date] <= [_THISROW].[End Date])
)
)
)
= 0)
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.
Thanks so much! I know it was simple, but slightly above my paygrade.
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |