No, I've no idea why it's not working for you, Polly.

If you are still struggling with the debugging it's sometimes worth
temporarily reformatting cells to General or Number, rather than Time, to
see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5,
18:00 would be 0.75, 15:00 would be 0.625, etc.
--
David Biddulph

"Polly" wrote in message
...
David, you are fabulous - separating them out, it works, putting them back
together it does not work. I can leave them separate, but do you have any
ideas as to why it wouldn't work putting them together?

"David Biddulph" wrote:

I used your original values and got 0:57 inside and 0:13 outside. I also
tried other values, looking at both ends of the day, and got the right
answers.

Perhaps you've mistyped one or more of your values? To debug your data,
try
to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1).
--
David Biddulph

"Polly" wrote in message
...
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.