Hi all, I have two date columns named as 'beg...

[Alper]

Hi all, I have two date columns named as ‘begin’ and ‘end’, formatted mm/dd/yyyy. When user wants to enter these dates for a new record, I want to prevent entry;

if either of start or end date falls ON or WITHIN any of the start-end date ranges in any existing rows, or; entered same dates (‘end’ must be a day later, at least).

In other words, I want new start-end days range entered into a completely unallocated space, in a monthly calendar. Of course, a gannt chart, or at least a calendar view with occupied days marked, would be very helpful here for user

(I voted for gannt chart, btw ). The solutions i can think of, involve calculations on several extra columns, but feeling this can be done in lesser steps. How this can be accomplished, any ideas? ie, converting all ranges to a large list and check against this list, if such a way possible…

[Aleksi_Alkio]

Before going too deep…if you need just a validation, you should be able to do that with a simple formula like… AND( COUNT(SELECT(Table[Key], OR( AND( [_THISROW].[Start]<[Start], [_THISROW].[End]<[Start]), AND( [_THISROW].[Start]>[End], [_THISROW].[End]>[End]) ) ))=COUNT(Table[Key]), [End]>[Start])

UPDATED

[Alper]

@Aleksi_Alkio Hello, I have tried variations of above formula in validif() of [start] column, but it accepted none of the tested date values (using a form). If I convert all four signs to “>”, then it works, just to prevent entering a date before the latest date available, which can be done some other ways, and not enough to solve the issue. The problem comes from cases where we need to enter dates, into spaces available between existing date ranges. Like, 1-15 Jan and 1-15 Feb occupied and we want to enter 16-25 Jan. Not sure if this can be solved without using if() in the formula, or may be having list of all days between oldest start day and latest end day, then list items filled with 1s and 0s to create a comparison template. Quite a puzzling one for me, wondering how booking sites doing it.

[Alper]

May this help me ? Need true/false version instead of result range, but dont know how to (and assuming Appsheet lookup works same way).

[Steven_Coile]

(COUNT(

FILTER(

“MyTable”,

AND(

([_THISROW].[Begin] >= [Begin]),

([_THISROW].[End] <= [End])

)

) ) > 0)

[Steven_Coile]

What does “completely unallocated space” mean?

[Alper]

+Steve Coile meant, empty (unallocated) days.

[Steven_Coile]

Note that the double underscore in front of THISROW should be trimmed to only a single underscore in use.

[Alper]

+Steve Coile

Hello Steve, this is very nicely formulated, thank you. Sounds like a good candidate for Filter() examples.

(After few hours of struggle, realised that formula fails if existing dates already overlaps even once, in table)

[Steven_Coile]

Example updated to catch existing ranges that entirely enclose the new one, not just those that start or end within the new range. Please review.

https://help.appsheet.com/expressions/functions/and

[Alper]

+Steve Coile

Hi, made some tests, all works very well, as long as dates are not ruined manually (to overlap).

In this point I am wondering if offline usage may cause any problems on creation of overlapping dates? I didn’t make tests of offline usage yet. David Clemons have a nice example in UI/UX howto section for calendar view application, I will try to do something similar, to at least visually ensure user will not try to select any overlapping date, by using those colorlines under dates as a guide.

And one last point about AND() function, irrelevant but, in help.appsheet pages, the search bar at top, cannot search for AND /or/ AND(). Wanted to check your updates, couldnt find it by searchbar, just to inform.