Calculate how much time falls between set start and stop times

[Polly]

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.

 

[Pete_UK]

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

Pete

 

[Polly]

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

 

[David Biddulph]

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.

 

[Polly]

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

 

[Polly]

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?

 

[Polly]

David,

Did you try anything with your start time after 15:00? When I have that
situation, my "in block" result is a negative number so when I subtract the
elapsed time from it for the "outside block" result I'm getting the wrong
answer.

Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40
Min = 15:00, Max = 17:56
Inside Block = -176
Outside Block = 216

If I sum the Inside and Outside Blocks, then I get the right answer - but I
shouldn't have to do that, right? I'm sorry, I've never used Min and Max
before so I'm unfamiliar with their purpose.

I truly do appreciate the education.

Polly

 

[David Biddulph]

Yes, valid point.
Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1))

 

[Polly]

PERFECT!!!!

David - I don't know who you are, where you are, or what you do for a
living, but you just saved me a BOATLOAD of time every month.

Thank you so very much.

Polly

 

[Polly]

One last question - I promise.

Can I combine this with an IF statement?

For two of my 'blocks' I have different days of the week with different
times. So what I want to say is if DOW = X (2, 3, 4, 5, 6) then use Y and Z
for my Min and Max (M$1 and L$1 - obviously I will have different columns for
the different days of the week, so it will look something like...

if(A2(day of the week) = 2, original Min Max formulas, if(A2 = 3, new Min
Max formula, if(A2=4, new Min Max formula, etc.))))

It seems like it ought to work, but I thought I'd ask before I spent time on
it.

Thanks again.

 

[David Biddulph]

Yes, you could either use an IF statement as you suggest, or otherwise use
either CHOOSE or VLOOKUP functions. There should be a number of ways of
getting the answer you're looking for.

 

[David Biddulph]

Glad to have been able to help.

 

[Robin]

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.

 

[Robin]

I am using =Text(end time-start time,"[m]")
the answer is a number of minutes, if "" is used the answer is seconds
alsoa number. Similarly "[h]" can be used

 

[Robin]

I am using =Text(end time-start time,"[m]")
the answer is a number of minutes, if "" is used the answer is seconds
alsoa number. Similarly "[h]" can be used

 

[Robin]

I use =text(end time-start time,"[m]") this give a number in minutes, if seconds are required use ""

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.