Can you explain what you mean by overlapping?
- Matching start and end dates?
- Matching start or end date?
- Start date between an existing start and end date?
- End date between an existing start and end date?
Here is an example that can be used in a Formula - Checkbox field to indicate if the entered start and end dates both fall within an existing range.
// Define Variables
var date startDate = [Start Date];
var number startDateFID = 13; // Field ID of the above field
var date endDate = [End Date];
var number endDateFID = 14; // Field ID of the above field
// Define Query
// Searches for existing records where the [Start Date] is BEFORE the current record [Start Date] and the existing [End Date] is AFTER the current [End Date]
var text query = "{" & $startDateFID & ".BF.'" & $startDate & "'} AND {" & $endDateFID & ".AF.'" & $endDate & "'}";
// Run the query and count the results. If the query returns a result, tick the checkbox
If (Size(GetRecords($query)) > 0, true)
------------------------------
Matt Hardy
------------------------------
Original Message:
Sent: 03-06-2022 15:08
From: D S
Subject: Overlapping dates
Could you please tell what that formula query look like?
I am also in somewhat similar situation. I have records with fields start date and end date. I want to add a custom data rule, to not allow creating records that overlap existing records
thank you!
------------------------------
D S
Original Message:
Sent: 03-01-2022 11:28
From: Mark Shnier (YQC)
Subject: Overlapping dates
This can likely be done with formula queries.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
Original Message:
Sent: 03-01-2022 11:25
From: Francesco Spiga
Subject: Overlapping dates
Hi Nathaniel, I have been looking to create an overlapping report based on date/time fields as well.
Is there a function in QB that can reproduce the same results of the SUMPRODUCT excel formula?
Sadly, I have not found a solution yet...
------------------------------
Francesco Spiga
Original Message:
Sent: 09-05-2019 16:36
From: Nathaniel Cahill
Subject: Overlapping dates
I have an out of office table. I need to check that new records don't overlap with existing records. Any way to do this natively?
The most recent post on this I could find was from 2017, and it pointed to an AJAX solution, not a native one.
------------------------------
Nathaniel Cahill
------------------------------