Hello,

I ' trying to find a formula for the following situation.

I have 2 adjacent intervals (length, in meters). Each interval has an upper
and a lower limit. Each interval has it's own row and each of these rows has
it's corresponding upper and lower limits, each in it's own cell.

Every day work progress is uploaded into a table. This work progress gives
the start and end points of the daily work. One day work can take place only
in Interval 1 or 2; the following day it might start inside Interval 2 and
end in Interval 1; and the day after it it might start in Interval 2 and end
in Interval 1 (the opposite of the previous case).

I need a formula that takes the daily start and end points and calculate how
much distance was covered for each interval.

For example:

Interval 1: from 223 to 347 meters.
Interval 2: from 347 to 510 meters.

Daily Progress for today: from 329 to 419 meters.

I need a formula that does the following calculations:

Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329
(lower limit for daily progress) = 18 meters
Length of progress in Interval 2 = 419 (upper limit for daily progress) -
347 (lower limit for Interval 2) = 72 meters

And I need to take into consideration what I mentioned previously:

- One day work can take place only in Interval 1 or 2;
- The following day it might start inside Interval 2 and end in Interval 1;
- The day after it it might start in Interval 2 and end in Interval 1.


Thank you very much for all your help!

--
igor