Calculate how much time falls between set start and stop times

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

"David Biddulph" wrote:

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

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

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.

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" wrote:

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

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

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.

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

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

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

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

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.

Glad to have been able to help.
--
David Biddulph

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

"David Biddulph" wrote:

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

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

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.

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