Overlapping dates

Question

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
------------------------------

francescospiga1 · Answer

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
------------------------------

markshnier__you · Answer

This can likely be done with formula queries.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

ds · Answer

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 recordsthank you!
------------------------------D S------------------------------

matthardy · Answer

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 = "{" &amp; $startDateFID &amp; ".BF.'" &amp; $startDate &amp; "'} AND {" &amp; $endDateFID &amp; ".AF.'" &amp; $endDate &amp; "'}";

// Run the query and count the results. If the query returns a result, tick the checkbox
If (Size(GetRecords($query)) &gt; 0, true)​
------------------------------Matt Hardy------------------------------

ds · Answer

Thank you, Matt.What formula query would be for&nbsp;

Start date between an existing start and end date?
End date between an existing start and end date?

Can it be achieved through IR?------------------------------DS------------------------------

Forum Discussion

Overlapping dates

6 Replies

Related Content

Date Input

Overlapping Time formula

Formula to display someone's age based on date?

Date Add Formula

Date to show as MMM YY

Recent Discussions

email attachments

API_GenAddRecord then redirect to a report

Live Chat with Others in The Qrew in Discord

Calendar Reports not Loading

When the Expiration Date is On or Before Today, Change the Contract Status to Expired