How to check if a date within a new date range (from date - to date) already exists in table?

[Metworks_IT]

Hello, I was hoping someone might have some ideas how to approach this problem:

I have an app - Attendance - where Worker logs their annual leave requests and is being processed by Supervisor, so far so good. EG from 12/Apr to 16/Apr, Annual Leave.

Supervisor also adds training dates on behalf of Worker - EG 22/Apr - Training - so Worker would see it on their calendar, gets reminders, etc, all good.

Problem: How do I check if a date in date range is already booked, to give a warning to Worker, if they want to book AL between 21/Apr to 25/Apr, to let them know that they were already booked for training, on 22nd?
And if they really need it, and they proceed, it would trigger an email to Supervisor to re-arrange training, etc.

If Worker would only request a day off, I have that covered, checking if said date is already booked. But just can’t figure it how to check within date ranges.
I have it working after sync, relying on auxiliary tables - currently used by Supervisor to monitor team training date - but I want to get that warning in the form, before Worker submits their AL.

Thank you all!

[1minManager]

So for the Valid IF formula I’d do:

Count(Select(Table[TableID],AND(
[Start Date]>=[_ThisRow].[Start Date],
etc…
)))>0

So if it finds an overlap the count is >0 and you can display an error message. Here’s a similar one in one of my Apps

COUNT(Select(Timesheet[TimesheetID],AND(
[TimesheetUser]=[_ThisRow].[TimesheetUser],
[Date]=[_ThisRow].[Date],
OR(
[Clock Out]=[_ThisRow].[Clock In],
[Clock In]=[_ThisRow].[Clock In],
[Clock Out]=[_ThisRow].[Clock Out],
[Clock In]=[_ThisRow].[Clock Out],
AND(
[Clock In]<[_ThisRow].[Clock In],
[Clock Out]>[_ThisRow].[Clock In],
[Clock Out]<[_ThisRow].[Clock Out]
),
AND(
[Clock In]>[_ThisRow].[Clock In],
[Clock In]<[_ThisRow].[Clock Out],
[Clock Out]>[_ThisRow].[Clock Out]
),
AND(
[Clock In]<[_ThisRow].[Clock In],
[Clock Out]>[_ThisRow].[Clock Out]
)
))))=0

[Metworks_IT]

Hi, thanks for sharing this. I had a similar approach, but looking at your expression, I think I didn’t cover all possible combinations, will have to add more conditions.
Thanks again!
Gabriel