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.

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.

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.

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.

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.

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.

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

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

"Robin" wrote:



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

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

"Robin" wrote:



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

"Robin" wrote:

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

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