Home / Blog / FileMaker / Determining if Two Date Ranges Overlap
31Mar 2016

Determining if Two Date Ranges Overlap

About the Author

Jeremy Brown Jeremy Brown

Jeremy is proud to be a FileMaker 12-15 certified developer. He has worked previously at a charter school network where he built the student information system used for three schools. As with many other developers, he stumbled into the world of FileMaker and has become fascinated with solving business needs in the platform. He is an amateur magician and is currently addicted to the game "Factorio".

Comments (12)

Ward - March 31, 2016

Jeremy thank you for the writeup. I had to solve these same problem for a price ranges, which is a bit easier. But I did not look into the inverse function. Great thinking! Great work. Thank for sharing.

    Jeremy Brown
    Jeremy Brown - April 1, 2016

    Thanks for the comment. I must admit, someone else already thought it for me. I just ‘discovered’ it and applied it to FileMaker. I’m sure it will work for price ranges too. That’s a great idea.

fdv - April 1, 2016

I don’t see demo!

    Jeremy Brown
    Jeremy Brown - April 1, 2016

    Hi. I didn’t post a demo because its rather rudimentary. The functions are listed in the post. But I can add the demo to the post. Thanks for the feedback.

Kevin Frank - April 13, 2016

Thought-provoking. Thank you.

Shreekrishna shankhwar - April 20, 2016

above example can be rectified as
select datediff( if(@ReportStartDate>=a.d_TermStartDate,@ReportStartDate,a.d_TermStartDate),if(@ReportEndDate <=a.d_TermEndDate,@ReportEndDate,a.d_TermEndDate) ) * -1

— Considration i have taken
— StartA = @ReportStartDate, EndA = @ReportEndDate
— StartB = d_TermStartDate, EndB = d_TermEndDate

this would be more easy . thanks for your help

Caroline - May 25, 2016

Thanks for sharing this Jeremy – it has saved me a lot of time on researching and has numerous applications in relation to the somewhat complex task of resource scheduling. A truly elegant solution.

Peter Wagemans - June 1, 2016

Great stuff. I applied it yesterday with time ranges, it works in exactly the same way. You’ve combined 2 great techniques in one fantastic blog article. Thank you.

    Jeremy Brown
    Jeremy Brown - June 22, 2016

    Thanks Peter for the kind words. I’m glad it works for Time Ranges as well.

Henning Orth - June 22, 2016

Brilliant! Just what I needed to work with my calendar, thanks for the effort and the explanations.

“But once the solution presents itself, the work can be very satisfying.”
–> Just enjoyed a moment of content seeing my validator-script running nicely 🙂

I ended up combining dates and times into one numerical so I could easily check for ranges over multiple days using the given formula.

    Jeremy Brown
    Jeremy Brown - June 22, 2016

    Hi Henning. Thanks for the kind words. I like your idea of combining the dates and times into one number.

Hamish - February 3, 2017

What a great solution Jeremy. I just tested a basic booking system including an available asset picker for the proposed booking.

I made a global booking date start and date end field . I used then your function on the existing booking dates v the proposed booking date globals. I added an asset id field for overlap items and another match for product id (using a product id global in the booking table). I then used a list summary field of the overlap asset id as the join to an assets TO. With a little refresh portal love I have an available Assets picker for the desired date range.

How would you do that? 🙂


Leave a Reply