Non overlapping date with time

Hey Everyone,

I came across steves document on the “Validate Non-Overlapping Date Range” in his AND() guide.

AND(
([End] > [Start]),
(COUNT(
FILTER(
“MyTable”,
OR(
AND(
([Start] >= [_THISROW].[Start]),
([Start] <= [_THISROW].[End])
),
AND(
([End] >= [_THISROW].[Start]),
([End] <= [_THISROW].[End])
)
AND(
([Start] < [_THISROW].[Start]),
([End] > [_THISROW].[End])
)
)
)
) = 0)
)

I would love to use this as a base for my app in booking sessions for clients.

Now the question is, if i wanted to use both date and time as a variable would it best to create two further columns with date and start timeconcatenated and date and end time concatenated, or does anyone know how i could start to build an expression to include the start and end times into this expression.

Regards
Ben

Best to use DateTime values rather than a combination of Date and Time values.

3 Likes

Thanks again for your time Steve.

So going down the route of adding in two new columns to concatenate the DateTime values would be best.

Regards
Ben

I prefer to collect the input from the user as a DateTime then split it out to Date and Time if needed, but either approach works.

1 Like

I never thought of having data collected that way, i thought it would have been better to collect it separately. I would much rather change the way i currently have it set up if its better to have it one way.

Why do you prefer to collect data as DateTime?

Regards
Ben

One data point, less screen real estate, easier to perform calculations with.

1 Like

Nice :slight_smile: Thanks for that, gonna go do some tweaking haha :slight_smile:

1 Like

Hey @Steve

I just wanted to run a few things by you in regards to this overlapping expression.

Although you said it is better to capture the DateTime, I am unable to do it this way as I’m still having issues with the way it handles time at 12noon. It still keeps displaying 00:00pm / 00:00am and I don’t want it to do that as it could get confusing to people, I did email tech support, but there was no outcome to that situation.

So, I am currently concatenating the values with this expression.

CONCATENATE(DATE([Date])," “,TIME([Time Start]))
CONCATENATE(DATE([Date]),” ",TIME([Time End]))

I then wanted to try and change your expression a little so that it would allow a start times to begin on end times and end times to finish on Start times. For example 12:30-13:00, 13:00-13:30 would be allowed in the validation.

So I tried this:

AND(
 ([End DateTime] > [Start DateTime]),
 (COUNT(
   FILTER(
     "Case Session",
     OR(
       AND(
         ([Start DateTime] > [_THISROW].[Start DateTime]),
         ([Start DateTime] < [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       ),
       AND(
         ([End DateTime] > [_THISROW].[Start DateTime]),
         ([End DateTime] < [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       )
       AND(
         ([Start DateTime] < [_THISROW].[Start DateTime]),
         ([End DateTime] > [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       )
     )
   )
 ) = 0)
)

Basically removing the = out, thinking that would work but it still doesnt seem to solve the issue. Can you see an error in the way I’m working on this?

Regards
Ben

Ooops, i think i have found my error. :grimacing:

AND(
 ([End DateTime] > [Start DateTime]),
 (COUNT(
   FILTER(
     "Case Session",
     OR(
       AND(
         ([Start DateTime] >= [_THISROW].[Start DateTime]),
         ([Start DateTime] < [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       ),
       AND(
         ([End DateTime] > [_THISROW].[Start DateTime]),
         ([End DateTime] <= [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       )
       AND(
         ([Start DateTime] < [_THISROW].[Start DateTime]),
         ([End DateTime] > [_THISROW].[End DateTime]), 
         ([Physio]	=	[_THISROW].[Physio]),
         ([Attendance]="Scheduled")
       )
     )
   )
 ) = 0)
)
2 Likes

Hey Everyone, I’m back on this again.

If I try to edit just the time of a pre-existing session with this expression, it thinks that a session is already in the system and the valid if constraint kicks in.

If I was to add into the filter a NOT([SessionKey]=[_THISROW].[Session Key])

Would something like that help the situation?

Regards
Ben

Im not reading all the posts in this thread but only your last one.

Does not make any sense, always return FALSE

1 Like