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 (11)

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.

Leave a Reply