David,

I see where you are going with this, and I tried it, but I'm not getting the
result I know I should be getting (always good to start with one you know the
answer to). I think the problem is coming from the MIN MAX bit. Maybe I
haven't explained the answer well enough. I have a block of time: 7:00 to
15:00, I have events that start after 7:00 and end before 15:00 - these are
easy, total time falls within the block. If an event starts before 7:00 and
ends before 15:00, then the bit before 7:00 is outside the block, and the bit
between 7:00 and 15:00 is inside the block. If an event starts between 7:00
and 15:00 and ends after 15:00, then again, some of the time falls inside the
block and some of the time outside the block. If the event starts after
15:00, then all of the time falls outside the block.

When I used your formulas, all of my time is falling outside the block.

I really appreciate your advice and hope you can help further.

Polly

"David Biddulph" wrote:

N2 is =MIN(J2,M$1)-MAX(I2,L$1)
O2 is =K2-N2 (assuming that K2 is =J2-I2)
Multiply by 24*60 if you want to convert from Excel times to minutes.
--
David Biddulph

"Polly" wrote in message
...
Pete:

Start time is in I2
Stop time is in J2
Elapsed time is in K2
The start time for the calculation (07:00) is in L1
The stop time for the calculation (15:00) is in M1
The formula will appear in N2 and O2 respectively (time inside the block
and
time outside the block)

So far I have an if statement that works if both the start and stop times
fall between the block (0700-1500), I'm getting hung up where either the
start time falls within the block but the end time doesn't, or vice
versa).

Polly




"Pete_UK" wrote:

Tell us what cells you are using, then the solution can be geared
directly for you.

Pete

On Apr 9, 4:12 pm, Polly wrote:
I have a fairly large amount of data involving time. I have a start
time, a
stop time and elapsed time. I need to know how much of the elapsed
time
falls between set times elsewhere in the spreadsheet.

Ex: Set times are 07:00 and 15:00
Start time = 14:03
End time = 15:13
Elapsed time = 70
The question I need to answer is, how much of the elapsed time falls
between
7:00 and 15:00 and how much falls outside. So the answer is (doing the
math
in my head ) is 57 minutes inside and 13 minutes outside.

But I need to do this for hundreds of rows of data and can't seem to
come up
with a formula that works.