Non overlapping date with time

AppSheet AppSheet Q&A
1

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

2

Ben_Rix:

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

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

3 Likes
3

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

4

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
5

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

6

Ben_Rix:

Why do you prefer to collect data as DateTime?

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

1 Like
7

Nice Thanks for that, gonna go do some tweaking haha

1 Like
8

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

9

Ooops, i think i have found my error.

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
10

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

11

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

Ben_Rix:

NOT([SessionKey]=[_THISROW].[Session Key])

Does not make any sense, always return FALSE

1 Like
12

Hello Steve, thanks for your help! And If I have slots with time? For examples… 14/11/2022 for date and time like similar slot: 08.00 - 09.00 - 10.00 - 11.00 - 12.00 - 13.00 - 14.00 how is possible? Thanks :slightly_smiling_face: