How to check if a Date & Time falls within a range and avoid double booking?

I initially thought I could easily validate it against the duration of the booking but that is not correct.
The users could bypass the validation by selecting a date between the FromDate - UntilDate

Anybody got a better idea how to approach this with the validation?

I been thinking about this all day but unable to put in actions.
Does you suggestion not look at the Time at all?? Because that is important to avoid double booking.

Although trying to follow your instructions, I am getting confused....

By the way, it is not a workflow but rather a validation in the form when submitting.

This is my current scenario:

CustomList:  PoolCar

The Form

FromDate and UntilDate are the Date/Time fields.

The Validation

The validation should block from booking Ferrari between 21/11/2018 07:00 - 27/11/2018 07:00.
How would I implement your instructions?

I believe you are trying to add the validation on the Form side and not the workflow, which would be after submission was made. I believe you can use a calculated control and use the Lookup function to retrieve a single car's start and end date. You then validate the start date for the end user booking to the start date/end date of the lookup WHEN the same car is selected.

The part you need to work out is how to retrieve all of the bookings that you then validate against. Maybe Lookup can be responsive to the drop down for the car. Or you can have a javascript function run on submit to do a custom validation and do the same lookup and prevent the save.

that might be a quite complex task with regular list and (nintex) forms. 

basically, what you need is a CAML query that looks for all the overlaps of already booked reservations with a datetime period of an event/reservation being booked. see example of such a query eg. here - February 2017 Mission: Real World Solution (Patching calendar) 

unfortunately, forms do not provide a way to do this easily. you'd need to write a javascript code that performs the query against the list. (maybe it might have been workable with web request control, but I hadn't played with that).

but I think there is a better/easier way how to implement this kind of logic in sharepoint.

create a resource calendar with single car(s) being resources. sharepoint will then automatically check for you that resource/car is not booked twice for the same time span.

see eg. these articles how to create such a calendar

Enable reservation of resources in a calendar - SharePoint 

https://www.dynamics101.com/how-to-create-a-resource-calendar-in-sharepoint-2010/ 

if you're on SP2013 you may need to perform an extra configuration step to make group-like features visible and configurable from site settings screens 

https://collab365.community/reservation-of-resources-in-sharepoint-2013-and-sharepoint-2013/ 

Hi,

I figure it is a complex solution but never gave up hope lol
At least I can use the List View to show current items but unfortunately unable to filter to show only between certain dates.

Anyway, the suggestion to add create calendar with resources:
I am using SP2016 and the group-like feature is not available.

Furthermore...

Anybody can assist me with IF & And validation formula? The purpose is to validate that the StartDate & EndDate on the form do not to fall between the StartDate & EndDate in the lookup list.

if FromDate is greater or equal to Date1 AND

FromDate is less or equal to Date2 

&&

if UntilDate is greater or equal to Date1 AND

UntilDate is less or equal to Date2

then show "NOK" or "OK".

I started but then got confused .... lol I assume there are a lot of IF's and AND's ?

and(if(and(formatDate(FromDate, "dd/MM/yyyy HH:mm")>=formatDate(Lookup("/list|Request","FromDate",FromDate, "FromDate"),"dd/MM/yyyy HH:mm"),formatDate(UntilDate, "dd/MM/yyyy HH:mm")<=formatDate(Lookup("/list|Request","UntilDate",UntilDate, "UntilDate"),"dd/MM/yyyy HH:mm")),"OK","NOK"), if(and(formatDate(UntilDate, "dd/MM/yyyy HH:mm")>=formatDate(Lookup("/list|Request","FromDate",FromDate, "FromDate"),"dd/MM/yyyy HH:mm"), formatDate(UntilDate, "dd/MM/yyyy HH:mm")<=formatDate(Lookup("/("/list|Request","UntilDate",UntilDate, "UntilDate"),"dd/MM/yyyy HH:mm")),"OK","NOK"))

The purpose is to say NOK when the FromDate is greater or equal to FromDate (in the lookup list) and at same time to check if the StartDate is less or equal to EndDate (in the look up list) > likewise for the UntilDate.

Any suggestions?

have you checked the latest link from above?

the same way it *should* be possible to enable group functionality on SP2016 (at least by what I've found, do not have SP2016 at hand to check it)

I would recommend to focus on a resource calendar solution I advised above.

I am running on SP2016 on-premises and I do not have the "resource calendar" but only "calendar" available where the resource table is not manageable and cannot edit or find it anywhere.

Similar issue: https://social.technet.microsoft.com/Forums/office/en-US/5edcb7f2-5d7e-4e4f-aad8-20125020a775/calendars-with-resources?forum=onlineservicessharepoint

Similar issue:

if you read through that document carefully you would find there the link the points to just the same document as I linked above and which describes how to enable resource list and group-like functionalities on SP2013/2016.

but maybe that's a task for your SP admin since it requires some configuration changes on SP itself.

• How to add a title to the PopUp window to say (WARNING)

that's not possible with OOTB javascript's dialog methods.

you'd need to call SP's dialog to be able to customize dialog, see - SP.UI.ModalDialog Methods | Microsoft Docs 

• How to make the alert show all the already booked dates instead of poping a window for each one
  (imagine you have 10 booked dates, current scenario the alert will be triggered 10 times continuesly)

move alert out of the loop. within the loop prepare just a message content to be shown afterwards

• Display the Date/Time in following format dd/MM/yyyy HH:MM in the popup alert

see various OOTB Date object's conversion/output methods - JavaScript Date Reference 

• How to also trigger the alert when the SUBMIT button is clicked

configure button's 'Client click' setting to make a call to your validation function

• Any other improvements to optimise the script?

not really improvements/optimizations, just few notes

- do not rely on client side validation like this. whatever effort you spent on single client, you can never avoid in concurrent environment that two or more clients/users do the booking at about the same time and book the same resource for the same time span in parallel. it may help to some extent, but it will never be 100% reliable

- you source in all the bookings at the form load. so all the bookings committed (or possibly released) during the form session will not be considered

Final Validation Script:
(which I paid for to be done... but am willing to share with you for free as a payback contribution)

var allItems = [];
var listName = 'P...Car Request'
NWF$(document).ready(function() {
retriveListItem();
NWF$('select[name*="DateHours"],select[name*="DateMinutes"],input.nf-date-picker').change(function() {
checkOldEntries();
});

});

function retriveListItem() {
    NWF$.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$select=*,PoolCar/CarRegistration&$orderby=FromDate&$expand=PoolCar&$top=5000",
        type: 'GET',
        headers: {
            "Accept": "application/json;odata=verbose"
        },
        cache: false,
        success: function(data) {
            for (var i = 0; i < data.d.results.length; i++) {
                var item = data.d.results[i];
                item.sDate = getDateLocalFormat(item.FromDate,-5);
                item.eDate = getDateLocalFormat(item.UntilDate,5);
                allItems.push(item);
            }
        },
        error: function(data) {
            console.log(error);
        }
    });
};

function getDateLocalFormat(inputDate,min) {
    //var add_10_minutes_to_current_date = Date.dateAdd(new Date(inputDate), -5, "h");
    return Date.dateAdd(new Date(inputDate), min, "m");
    //return new Date(inputDate);
};

Date.dateAdd = function(currentDate, value, timeUnit) {
    timeUnit = timeUnit.toLowerCase();
    var multiplyBy = {
        w:604800000,
        d:86400000,
        h:3600000,
        m:60000,
        s:1000
    };
    var updatedDate = new Date(currentDate.getTime() + multiplyBy[timeUnit] * value);
    return updatedDate;

};

function checkOldEntries() {
    //var count = 0;
     var allBookings='';
    var enteredSDate = new Date(ddmmyyTommddyy(NWF$('#' + validateFromDate).val()) + ' ' + NWF$('select[name*="DateHours"]:eq(0)').val() + ':' + NWF$('select[name*="DateMinutes"]:eq(0)').val());
    var enteredEDate = new Date(ddmmyyTommddyy(NWF$('#' + validateUntilDate).val()) + ' ' + NWF$('select[name*="DateHours"]:eq(1)').val() + ':' + NWF$('select[name*="DateMinutes"]:eq(1)').val());
    if (!(enteredSDate === undefined) && !(enteredEDate === undefined))
        NWF$.each(allItems, function(i, v) {
               if(v.PoolCar.CarRegistration == NWF$('#' + validatePoolCar).val().split('#')[1])
               {
                    
            if (((enteredSDate > v.sDate && enteredEDate < v.eDate) || (enteredEDate > v.sDate && enteredEDate < v.eDate) || (enteredSDate > v.sDate && enteredSDate < v.eDate)) || (enteredSDate <= v.sDate && enteredEDate >= v.eDate) ) {
                       if(allBookings==''){
                            allBookings='The selected pool car '+v.PoolCar.CarRegistration+' is already booked on 
';
                       }
                        allBookings+='
'+dateFormatddMMyyyyHHmm(v.sDate) +' >> '+dateFormatddMMyyyyHHmm(v.eDate);
                  console.log(v);
               }}
        });
          if(allBookings!=''){
                allBookings+='

Please choose a different date.';
               alert(allBookings);
               allBookings='';
          }
          
          
          
};
function dateFormatddMMyyyyHHmm(inputDate) {
    var d = new Date(inputDate),
        dformat = [ 
                    d.getDate().padLeft(),(d.getMonth()+1).padLeft(),
                    d.getFullYear()].join('/')+
                    ' ' +
                  [ d.getHours().padLeft(),
                    d.getMinutes().padLeft()].join(':');
                         //,d.getSeconds().padLeft()].join(':');
                         return dformat;
};
function ddmmyyTommddyy(inputDate) {
    var dateS = inputDate.split('/');
    return dateS[1] + '/' + dateS[0] + '/' + dateS[2];
};


Number.prototype.padLeft = function(base,chr){
   var  len = (String(base || 10).length - String(this).length)+1;
   return len > 0? new Array(len).join(chr || '0')+this : this;
}
    
$('#showit').click(function(){
   
     $('#result').html(dformat);
});
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks for sharing this.  Quick question: did you find a way to disable submitting the request after the popup was closed please?