Jump to content

Date range/overlap


This topic is 2851 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Good morning,

 

I wrote a small routine last week to report on some dates and had it working nicely. Stupidly, I fiddled with it and broke it - now have no record of my earlier work. :(  

What I have/had did this:

 

A client attendance range: 

Client_From (date field)

Client_to (date field)

 

A reporting range:

Report_From (date field)

Report_To (date field)

 

For example, how many days overlap with these two periods.

Client_From = 10/5/2016

Client_To = 13/6/2016

Report_From = 1/6/2016

Report_To = 30/6/2016

Overlap is 1/6/2016 to 13/6/2016, so result is 13 days

 

Client_From = 20/5/2016

Client_To = 18/7/2016

Report_From = 1/6/2016

Report_To = 30/6/2016

Overlap is 1/6/2016 to 30/6/2016, so result is 30 days.

 

You get it I’m sure.

 

My routine worked (was a Find), but thought a Custom Function might be suited better - parsing those 4 parameters and returning a number.

 

Is anybody aware of such a function please?  Many thanks in advance for any ideas.

 

Greg

Edited by Lee Smith
Changed the Font to make it readable.
Link to comment
Share on other sites

Hi Greg

You just need a calculation, although you could turn it into a custom function

Min ( client_to ; report_to ) - Max ( client_from ; report_from ) + 1

Sample file attached

overlap.fmp12

Edited by rwoods
Link to comment
Share on other sites

The general formula for calculating the overlap between two date ranges is =

Max ( Min ( Client_To ; Report_To ) - Max ( Client_From ; Report_From ) + 1 ; 0 )

 

10 minutes ago, rwoods said:

Min ( client_to ; report_to ) - Max ( client_from ; report_from ) + 1

That's going to produce a negative result when there is no overlap.

  • Like 1
Link to comment
Share on other sites

This topic is 2851 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.