31Mar 2016

Determining if Two Date Ranges Overlap

FileMaker work is rewarding. Puzzles arise from client needs, and the task of solving the problem can be tough. But once the solution presents itself, the work can be very satisfying. Not only does it solve a client’s need, but it is the culmination of some hard thinking, and that, in of itself, is worth it.

Case in Point

I was working with a client recently on reporting. We had some complicated ExecuteSQL-created, Virtual List reports that gathered data from all over the solution into one layout. Late in development, the client realized there was a need to see if two related date ranges overlap each other, and if they do, include the parent record in the report (see Figure 1).

Read about using the visual list technique: Part 1 and Part 2.

Figure 1 – Problem diagram

We made a sketch of the date ranges as shown in Figure 2. There were four possible combinations of two date ranges overlapping. It seemed like a lot of work to be able to compare date ranges with these four possibilities.

I wasn’t too thrilled about having to add to my already complex ExecuteSQL statement, so I decided to do what everyone does: google this problem to see if someone had a better way to see if date ranges do indeed overlap.

Figure 2 – Sketch of date ranges.

Researching Overlapping Date Ranges

In the FileMaker world, the search “overlapping date ranges” turned up some great advice by veteran developers on the various FileMaker forums. Those answers involved extra relationships and complicated queries. Using ExecuteSQL, I might be able to do this by joining the DateRangeA and DateRange B tables together and writing up the possible overlapping scenarios in the WHERE clause.

I turned my attention to programming, in general, to see if others had different solutions. It turns out people on other platforms have this problem. The StackOverflow community provided some useful answers, but they too involved a complicated SQL query.

Potential Solutions

One brief answer pointed to the best solution: Instead of trying to query for all the possible ways in which two date ranges could overlap, think of the scenarios in which they do not overlap. Low and behold, there are only two: Date Range A ends before Date Range B begins or Date Range A starts after Date Range B ends.

Figure 3 – No overlap

If one of these is true, then the two date ranges do not overlap. The simple formula is posted as:

(EndA <= StartB or StartA >= EndB)

The answer posted further pointed to a Wikipedia article on the math behind this theorem. It is a pretty interesting read, and I understood most of it.

Anyway, I was interested in the opposite if the result of the above calculation, so I rewrote it to return the inverse.

If ( NOT (EndA <= StartB or StartA >= EndB) ; “Overlap”)

This function will return “Overlap” when it is not true that the two date ranges do not overlap.

Like a magic trick, the revealed secret looks very simple and mundane. But this solves a huge problem for me. All I have to do feed into this calculation each date range start and stop time and the calculation lets me know if they overlap. If a parent record has two date ranges that overlap, the script will include that parent record on the report.

To test this out, I put together a demo file. I turned the calculation above into a custom function and passed in the start and end date of two date ranges. It returns TRUE if the date ranges do overlap.

_IsOverlap ( StartA ; EndA ; StartB ; EndB )

Demo File

I created some test records with start and end dates in the two ranges, fed in the parameters. Very simply, the calc determined if they overlapped.

Download the demo file

In the first example, the date ranges overlap.

Figure 4 – Date ranges overlap.

In the second, they do not overlap.

Figure 5 – Date ranges do not overlap.

Its amazingly simple but powerful!

In the example file I created, every date range has an end date. That may not always be true in the real world. If Date Range A is still active, the end date hasn’t been determined, so no date is in that field. I overcame that slight issue by passing in the absurd date of 1/1/4000 as the end date. That solved the issue.

Side note: This function works very well when I can compare dates set in the same country. I’m sure this would work well for other matching time units, but I did not focus my testing on other units of time.

Extending the Idea

The stackoverflow.com answer referred me to a cool extension of this work.

Here I learned that I can calculate the number of days in which the two date ranges overlap. It says that the number of days overlap will always be the minimum of one of the four calculations.

Min (
EndA - StartA ;
EndA - StartB;
EndB -  StartB;
EndB -  StartA
)

Practical Uses

All of the descriptions above are a bit abstract. However, this technique has potential to solve problems commonly found in FileMaker solutions. Here are a few.

Ensure that an appointment for a person does not conflict with already existing appointments.
Determine when two people are enrolled in the same course at the same time.
Determine if two time-off requests for the same person overlap, or if two requests in the same department overlap.

There are plenty more uses.

Conclusion

Looking outside of the FileMaker world into general programming, I was able to find a simple technique to a problem that seemed daunting when visualized. Using this technique in FileMaker, I was able to very easily see if two date ranges overlap. And, if I wanted to, I could determine the number of days the two ranges overlap.
This useful technique is simple yet powerful and has many possible uses.

Watch our videos for more FileMaker tips and techniques:

Moving Forward

Do you have any questions about other puzzles within your FileMaker solution? Our team of certified FileMaker developers has helped many organizations address complex challenges in their implementations, and we're happy to provide additional insights on how we can help your team. Contact us to set up a quick phone call today.