This site uses tracking cookies. By using this site, you agree to our Privacy Policy. If you don't opt in, some parts of the site might not function.
Blog
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 certified FileMaker 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, Jeremy stumbled into the world of FileMaker and has become fascinated with solving business needs in the platform.

Comments (19)

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.

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

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

Reply
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

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

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

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

Reply
    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.
    Thanks

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

Reply
James Glendinning - June 2, 2017

This is great – simple yet effective. Thanks for the CF’s too!

Reply
James Glendinning - June 2, 2017

Jeremy – I found a need to be able to compare a pair of dates against a whole set of other dates – and managed to do this by adapting your Custom Functions in the demo file into a new recursive function. Let me know if you’d like to see your modified demo file with the changes…

Reply
    Jeremy Brown
    Jeremy Brown - June 2, 2017

    Hi James.
    That’s awesome that this will work for comparing a pair of dates against a whole other set of dates. I’d love to see your demo. Send it to me at jbrown@soliantconsulting.com
    Thanks. I look forward to seeing it.
    (though I must admit you got me intrigued. I’m not the most fluent at writing recursive CFs, so I’m going to tackle it as well and see if I can figure it out! Such a fun thing to do on a Friday afternoon.)

Patrick Charlebois - August 9, 2017

Thanks, this has been very useful. I did this many times with listing all the possible ways it can overlap, but I decided to google this time and I never thought of doing it in reverse, it’s so much simpler (and can basically be summarized with “if one of them ends before the other start, it doesn’t overlap”, which is easy to remember and quite logical)

Depending on your definition of overlap though the formula can cause an issue.

If you say that it does not overlap when (EndA = EndB), it will consider that there is no overlap when they end up on the same date.

For instance, 2017-08-01 to 2017-08-05 does not overlap with 2017-08-05 to 2017-08-10. But this is not necessarily the behavior you want (it was not in my case), so it might be good to add a note about it. In my case the formula I wanted was (EndA EndB), because I consider one range ending on the day the other one starts to be an overlap (and it seems to me that in many cases that’s how you’d want to consider it too).

If you want to look if hotel rooms are available, you want the >= because a reservation ending on the 3rd and another starting on the 3rd is not an overlap.

But if you want to look at say if a special price applies, well if it ends on the 3rd, it overlaps with the 3rd.

Sorry if none of these examples are in anyway related to filemaker!

Reply
    Jeremy Brown
    Jeremy Brown - August 9, 2017

    Hi Patrick,

    I agree with your comments in this and the next post. Since it depends on what ‘overlap’ means for your particular use case, then the adjustment will have to be made. I’ll update the post and the file.

Patrick Charlebois - August 9, 2017

Part of it came out wrong, it thought I was using tags and removed the formula and I can’t edit.

If it’s not clear, I was saying that sometimes you want > instead of >= and < instead of <=.

Hope it comes out fine this time…

Reply
KR - October 19, 2018

Thanks… you made it so so easy.

Reply

Leave a Reply