The solution below will only work when creating a new shift record for a given staff member, and comparing that shift’s start time against the rest for that staff member, which is what it sounds like you want. The other caveat is that it won’t (and can’t) put the clashing indicator on the new record, but rather on any shift record that it clashes with. Here’s how it looks in my test:
The latest shift start time for Pete falls in the middle of his first shift, so that first shift is tagged as clashing.
First off, to make this a bit easier, I made a field called {Last Start} that extracts the last start time—the one you’re currently adding—from the contents of {All Allocated Start Times}. This needs to be done twice in the main formula, so rather than put the bulky code in there twice, I made the {Last Start} field to act as a variable of sorts. Here’s the formula for that field (which you can hide once it’s all working):
DATETIME_PARSE(RIGHT({All Allocated Start Times} & "", LEN({Start Time})), "YYYY-MM-DDTHH:mm:ss.SSSZ")
And here’s my modified version of your formula to add the new check:
IF(
OR(
(
LEN(
ARRAYJOIN(
{All Allocated Start Times}
)
)-LEN(
SUBSTITUTE(
ARRAYJOIN(
{All Allocated Start Times}
),
{Start Time},
""
)
)
)/LEN(
{Start Time}
) > 1,
IF(
AND(
{Start Time},
{End Time}
),
AND(
IS_AFTER(
{Last Start},
{Start Time}
),
IS_BEFORE(
{Last Start},
{End Time}
)
)
)
),
"Clashing"
)
Duplicate {Last Start}, rename it to {Last End}, and use this formula:
DATETIME_PARSE(RIGHT({All Allocated End Times} & "", LEN({End Time})), "YYYY-MM-DDTHH:mm:ss.SSSZ")
With that done, this is the new primary formula, which will flag as clashing if either a start or end time for the last-entered shift falls between any other shift’s start/end combination.
IF(
OR(
(
LEN(
ARRAYJOIN(
{All Allocated Start Times}
)
)-LEN(
SUBSTITUTE(
ARRAYJOIN(
{All Allocated Start Times}
),
{Start Time},
""
)
)
)/LEN(
{Start Time}
) > 1,
IF(
AND(
{Start Time},
{End Time}
),
OR(
AND(
IS_AFTER(
{Last Start},
{Start Time}
),
IS_BEFORE(
{Last Start},
{End Time}
)
), AND(
IS_AFTER(
{Last End},
{Start Time}
),
IS_BEFORE(
{Last End},
{End Time}
)
)
)
)
),
"Clashing"
)
The order we assign the shifts seems to be primarily important here.
If we schedule chronologically, Tom’s 10:30 shift causes a clash with his 9:00, as it should, since the both the start and end time are within the earlier shift.
If we schedule him on the 10:30 and then the 9:00 (or even replace him on the clashing shift with himself), it won’t flag the clash, since neither the start nor end time are within the 10:30 shift, but the time inbetween is.
All I can think of is to add some conditions to the formula to check if other times between the start and end cause clashes too, but I’m hoping there’s some hidden logic to this puzzle that that will avoid having to brute force the IF formulas.
