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

 

[David Biddulph]

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).

 

[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?

 

[David Biddulph]

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.

 

[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.