Valid if formula listing all available staff not in overlapping shifts

Hey All,

I am trying to use a valid if formula to list all staff.
That's nice and easy with SELECT(Staff[ID],TRUE).

The issue I have is that this person may already be assigned on an overlapping shift.
So I made this up but its only working if you select an invalid person and then it will disappear.

SELECT(Staff[ID],
NOT(IN([ID],
SELECT(Shifts[Staff 1 Assigned],
AND(
[Scheduled Date] = [_THISROW].[Scheduled Date],
[Scheduled Start Time] < [_THISROW].[Scheduled End Time],
[Scheduled End Time] > [_THISROW].[Scheduled Start Time]
)
)
)
)
)

Any ideas how I could reduce the list without entering the staff member in the current form?

Solved Solved
0 1 46
1 ACCEPTED SOLUTION

Whoops, it does work, I was looking at wrong field haha.

View solution in original post

1 REPLY 1

Whoops, it does work, I was looking at wrong field haha.

Top Labels in this Space