[Question] Excel again

Cog

Cog

Ok, this is going to be hard for me to translate so please use your imagination.

I have a list of date ranges. I need to find if all those dates have one intersection point in common and what would be the range of that intersection.

Any ideas?
 
PatrThom

PatrThom

Are you saying you have a list of ranges A-B, C-D, E-F, etc.? Or are you saying you have multiple lists which each contain values bounded by a maximum and minimum?
Also, when you say you are looking for an intersection, do both lists actually have to contain the value (13589/25670) or does there just have to be overlap (1278/3490 could have had overlap from 3-8, if the values were present).
Also, does it matter if there are multiple intersections?

--Patrick
 
Cog

Cog

stienman said:
In excel, dates are just numbers. Days, specifically. Fractional parts provide time of day. For instance, today, May 19, 2014, is 41778.

Put each date range into two columns, side by side, one range per row:

5/1/2014 5/18/2014
5/3/2014 5/24/2014
5/12/2014 5/15/2014
5/7/2014 6/30/2014
etc

Now if they all overlap at least a little bit, then the highest date in the left column will be lower than the lowest date in the right column.

So you can use max() and min() to find out:

=max(A1:A4) =min(B1:B4)

This will return two numbers. You can format the cells as dates to get the dates they represent.

If the max is smaller than or equal to the min, then all the ranges overlap by at least one day (inclusive). If not, then there's one or more ranges which do not overlap the rest of the group. You can sort by the left and right columns to find out the max and min that cause a problem, if there is one.

If there is overlap, then you do end up with the date range in the max/min calculations.

If the ranges include time (fractional part of the number) then it will still work correctly and give you an exact timeframe for overlap.
Click to expand...
It works! Thank you.
 
You must log in or register to reply here.
Top