hi all
i came from steve Validate Non-Overlapping Date Range
i use the formula to insure of any new row doesn’t exist in same ranges of any rows
and its very worked good
my problem its when i try to edit any record tell me “this entry is invalid”
i think that’s because the current date range is still active in this row
so i need to get help with any strategy can solve this (update record with same dates)
AND(
([to] > [from]),
(COUNT(
FILTER(
"sessions",
OR(
AND(
([from] >= [_THISROW].[from]),
([from] <= [_THISROW].[to])
),
AND(
([to] >= [_THISROW].[from]),
([to] <= [_THISROW].[to])
)
AND(
([from] < [_THISROW].[from]),
([to] > [_THISROW].[to])
)
)
)
) = 0)
)
AND(
([to] > [from]),
(COUNT(
FILTER(“sessions”,
OR(
AND(
([from] >= [_THISROW].[from]),
([from] <= [_THISROW].[to])
),
AND(
([to] >= [_THISROW].[from]),
([to] <= [_THISROW].[to])
)*
AND(
([from] < [_THISROW].[from]),
([to] > [_THISROW].[to])
)
)
)
) = 0)
)
Where I’ve put an asterisk you’re missing a comma
I suggest laying out long formulas like this to make them easier to read. Also consider using software like Notepad++ for windows. Since it lets you do this but also highlights missing brackets etc
thanks alot i will work on that
User | Count |
---|---|
42 | |
25 | |
24 | |
19 | |
15 |