Date range/overlap

[Greg Hains]

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 June 6, 2016 by Lee Smith
Changed the Font to make it readable.

[rwoods]

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 June 6, 2016 by rwoods

[comment]

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.

[Greg Hains]

Good evening,

Once again thank you - this worked exactly how I wanted it to.  :-)
You guys (and gals) are terrific. 

Greg