I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if anyone
else had input. I know it is generally frowned upon to report the same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is that
originally the time ins and time outs started in column E. We had to add one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if you had
the original formula before the added time columns, it might help you or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it out it
won't be until tomorrow. I'm getting ready to call it a day. I'll play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the response and thanks for the formula correction. I was
testing an If stmt on the formula and forgot to take off the last argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I. T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15 min
increments down column A and B. The formula needs to calculate the amount
of
time each individual was clocked in/out from the earlier table over each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at 1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p should
read "0" or i have it formatted to "-". This time in and time outs can
happen 4 times in the day so having the incremental calculations knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Ok, so the background is below....i have edited the proposed formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in cells C10
and
D10....I need the formula to continue for time in/outs in cells E10:J10
and
then allocate to the correct 15 minute increment time frame....i hope
this
makes sense...let me know if you need more info.....THANKS FOR ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
T.
Thanks for the response...see my response to Sean for more detail....I
need
it to calculate in a decimal representation of an hour...e.g. 1/2 hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed of 15
minute
increments...The formula is part of a grid that looks like this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until
4:45 p 5:00 p

It sources from a "Time In" and "Time Out" section. Users record
time,
for
example, like this: 7:30 a for Time in and 12:00 p for time out.
The
formula
in question then fills in 15 minute ranges with the amount of time.

I added another set of time in and time outs so I have added two
columns
to
the front of that section. I need the new columns to calculate in
the
formula. The cells in question for the formula below would be C10
(time
in)
and D10 (time out).


=IF($F$10($A55),IF($E$10$B55,0,IF($A55=$E$10,IF ($F$10<$B55,($F$10-$B55)*1440/60,0.25),IF($F$10$A55,($B55-$E$10)*1440/60))),0)+IF($H$10($A55),IF($G$10$B55,0,IF($A55= $G$10,IF($H$10<$B55,($H$10-$A55)*1440/60,0.25),IF($H$10$B55,($B55-$G$10)*1440/60))),0)+IF($J$10($A55),IF($I$10$B55,0,IF($A55= $I$10,IF($J$10<$B55,($J$10-$A55)*1440/60,0.25),IF($J$10$B55,($B55-$I$10)*1440/60))),0)

Any thoughts??? Let me know if you need more info if I am not
clear.
Thanks!
-crm






--
-CRM
--
-CRM







--
-CRM

I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if
anyone
else had input. I know it is generally frowned upon to report the same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is that
originally the time ins and time outs started in column E. We had to add
one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if you
had
the original formula before the added time columns, it might help you or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the response and thanks for the formula correction. I was
testing an If stmt on the formula and forgot to take off the last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I.
T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15 min
increments down column A and B. The formula needs to calculate the
amount
of
time each individual was clocked in/out from the earlier table over
each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at 1:00
p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p
should
read "0" or i have it formatted to "-". This time in and time outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Ok, so the background is below....i have edited the proposed formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in cells
C10
and
D10....I need the formula to continue for time in/outs in cells
E10:J10
and
then allocate to the correct 15 minute increment time frame....i
hope
this
makes sense...let me know if you need more info.....THANKS FOR ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an hour...e.g. 1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed of 15
minute
increments...The formula is part of a grid that looks like this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until
4:45 p 5:00 p

It sources from a "Time In" and "Time Out" section. Users
record
time,
for
example, like this: 7:30 a for Time in and 12:00 p for time out.
The
formula
in question then fills in 15 minute ranges with the amount of
time.

I added another set of time in and time outs so I have added two
columns
to
the front of that section. I need the new columns to calculate
in
the
formula. The cells in question for the formula below would be
C10
(time
in)
and D10 (time out).


=IF($F$10($A55),IF($E$10$B55,0,IF($A55=$E$10,IF ($F$10<$B55,($F$10-$B55)*1440/60,0.25),IF($F$10$A55,($B55-$E$10)*1440/60))),0)+IF($H$10($A55),IF($G$10$B55,0,IF($A55= $G$10,IF($H$10<$B55,($H$10-$A55)*1440/60,0.25),IF($H$10$B55,($B55-$G$10)*1440/60))),0)+IF($J$10($A55),IF($I$10$B55,0,IF($A55= $I$10,IF($J$10<$B55,($J$10-$A55)*1440/60,0.25),IF($J$10$B55,($B55-$I$10)*1440/60))),0)

Any thoughts??? Let me know if you need more info if I am not
clear.
Thanks!
-crm






--
-CRM
--
-CRM







--
-CRM

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if
anyone
else had input. I know it is generally frowned upon to report the same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is that
originally the time ins and time outs started in column E. We had to add
one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if you
had
the original formula before the added time columns, it might help you or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the response and thanks for the formula correction. I was
testing an If stmt on the formula and forgot to take off the last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I. T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15 min
increments down column A and B. The formula needs to calculate the
amount
of
time each individual was clocked in/out from the earlier table over
each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p
should
read "0" or i have it formatted to "-". This time in and time outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in cells
C10
and
D10....I need the formula to continue for time in/outs in cells
E10:J10
and
then allocate to the correct 15 minute increment time frame....i
hope
this
makes sense...let me know if you need more info.....THANKS FOR ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an hour...e.g. 1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed of
15
minute
increments...The formula is part of a grid that looks like
this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until
4:45 p 5:00 p

It sources from a "Time In" and "Time Out" section. Users
record
time,
for
example, like this: 7:30 a for Time in and 12:00 p for time
out.
The
formula
in question then fills in 15 minute ranges with the amount of
time.

I added another set of time in and time outs so I have added
two
columns
to
the front of that section. I need the new columns to calculate
in
the
formula. The cells in question for the formula below would be
C10
(time
in)
and D10 (time out).


=IF($F$10($A55),IF($E$10$B55,0,IF($A55=$E$10,IF ($F$10<$B55,($F$10-$B55)*1440/60,0.25),IF($F$10$A55,($B55-$E$10)*1440/60))),0)+IF($H$10($A55),IF($G$10$B55,0,IF($A55= $G$10,IF($H$10<$B55,($H$10-$A55)*1440/60,0.25),IF($H$10$B55,($B55-$G$10)*1440/60))),0)+IF($J$10($A55),IF($I$10$B55,0,IF($A55= $I$10,IF($J$10<$B55,($J$10-$A55)*1440/60,0.25),IF($J$10$B55,($B55-$I$10)*1440/60))),0)

Any thoughts??? Let me know if you need more info if I am not
clear.
Thanks!
-crm






--
-CRM
--
-CRM







--
-CRM

Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a value
for each time period therefore resulting in a 1 as the result. Do we need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if
anyone
else had input. I know it is generally frowned upon to report the same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is that
originally the time ins and time outs started in column E. We had to add
one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if you
had
the original formula before the added time columns, it might help you or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the response and thanks for the formula correction. I was
testing an If stmt on the formula and forgot to take off the last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I. T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15 min
increments down column A and B. The formula needs to calculate the
amount
of
time each individual was clocked in/out from the earlier table over
each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p
should
read "0" or i have it formatted to "-". This time in and time outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in cells
C10
and
D10....I need the formula to continue for time in/outs in cells
E10:J10
and
then allocate to the correct 15 minute increment time frame....i
hope
this
makes sense...let me know if you need more info.....THANKS FOR ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an hour...e.g. 1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed of
15
minute
increments...The formula is part of a grid that looks like
this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until
4:45 p 5:00 p

It sources from a "Time In" and "Time Out" section. Users
record
time,
for
example, like this: 7:30 a for Time in and 12:00 p for time
out.
The
formula
in question then fills in 15 minute ranges with the amount of
time.

I added another set of time in and time outs so I have added
two
columns
to
the front of that section. I need the new columns to calculate
in
the
formula. The cells in question for the formula below would be
C10
(time
in)
and D10 (time out).


=IF($F$10($A55),IF($E$10$B55,0,IF($A55=$E$10,IF ($F$10<$B55,($F$10-$B55)*1440/60,0.25),IF($F$10$A55,($B55-$E$10)*1440/60))),0)+IF($H$10($A55),IF($G$10$B55,0,IF($A55= $G$10,IF($H$10<$B55,($H$10-$A55)*1440/60,0.25),IF($H$10$B55,($B55-$G$10)*1440/60))),0)+IF($J$10($A55),IF($I$10$B55,0,IF($A55= $I$10,IF($J$10<$B55,($J$10-$A55)*1440/60,0.25),IF($J$10$B55,($B55-$I$10)*1440/60))),0)

Any thoughts??? Let me know if you need more info if I am not
clear.
Thanks!
-crm






--
-CRM
--
-CRM

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a
value
for each time period therefore resulting in a 1 as the result. Do we need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if
anyone
else had input. I know it is generally frowned upon to report the
same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is
that
originally the time ins and time outs started in column E. We had to
add
one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if
you
had
the original formula before the added time columns, it might help you
or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it
out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll
play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Valko,
Thanks for the response and thanks for the formula correction. I
was
testing an If stmt on the formula and forgot to take off the last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I. T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15
min
increments down column A and B. The formula needs to calculate the
amount
of
time each individual was clocked in/out from the earlier table over
each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p
should
read "0" or i have it formatted to "-". This time in and time
outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in
cells
C10
and
D10....I need the formula to continue for time in/outs in cells
E10:J10
and
then allocate to the correct 15 minute increment time frame....i
hope
this
makes sense...let me know if you need more info.....THANKS FOR
ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an hour...e.g.
1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the
range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed
of
15
minute
increments...The formula is part of a grid that looks like
this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until
4:45 p 5:00 p

It sources from a "Time In" and "Time Out" section. Users
record
time,
for
example, like this: 7:30 a for Time in and 12:00 p for time
out.
The
formula
in question then fills in 15 minute ranges with the amount
of
time.

I added another set of time in and time outs so I have added
two
columns
to
the front of that section. I need the new columns to
calculate
in
the
formula. The cells in question for the formula below would
be
C10
(time
in)
and D10 (time out).


=IF($F$10($A55),IF($E$10$B55,0,IF($A55=$E$10,IF ($F$10<$B55,($F$10-$B55)*1440/60,0.25),IF($F$10$A55,($B55-$E$10)*1440/60))),0)+IF($H$10($A55),IF($G$10$B55,0,IF($A55= $G$10,IF($H$10<$B55,($H$10-$A55)*1440/60,0.25),IF($H$10$B55,($B55-$G$10)*1440/60))),0)+IF($J$10($A55),IF($I$10$B55,0,IF($A55= $I$10,IF($J$10<$B55,($J$10-$A55)*1440/60,0.25),IF($J$10$B55,($B55-$I$10)*1440/60))),0)

Any thoughts??? Let me know if you need more info if I am
not
clear.
Thanks!
-crm






--
-CRM
--
-CRM

Valko!
You are awesome!!! I got this to work when I copied down the first column
within the same name but I am having difficulty pulling it across rows as the
"names" change. Any thoughts??? Thanks!
--
-CRM


"T. Valko" wrote:

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a
value
for each time period therefore resulting in a 1 as the result. Do we need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer inspection
this won't be that complicated but the formula will be kind of long but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
I am reposting this discussion on a question I have been working on for
several days below. It seems to be quite a challenge. Just seeing if
anyone
else had input. I know it is generally frowned upon to report the
same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is
that
originally the time ins and time outs started in column E. We had to
add
one
more T.I./T.O. set so they got put in columns C & D. I inherited this
worksheet so I am having difficulty editing it. I thought perhaps if
you
had
the original formula before the added time columns, it might help you
or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure it
out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll
play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Valko,
Thanks for the response and thanks for the formula correction. I
was
testing an If stmt on the formula and forgot to take off the last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I. T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has 15
min
increments down column A and B. The formula needs to calculate the
amount
of
time each individual was clocked in/out from the earlier table over
each
15
min timeframe.

A B C D E ..... R
Sally Ben Chris Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15 min
increments between 8 and 9 will read 0.25....then she retrurned at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00 p
should
read "0" or i have it formatted to "-". This time in and time
outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in
cells
C10
and
D10....I need the formula to continue for time in/outs in cells
E10:J10
and
then allocate to the correct 15 minute increment time frame....i
hope
this
makes sense...let me know if you need more info.....THANKS FOR
ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an hour...e.g.
1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the
range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I have a formula that calculates the amount of time elapsed
of
15
minute
increments...The formula is part of a grid that looks like
this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until

That's where the real complication comes into play.

I used the same formula for each name and then manually changed the row
references. You could write the formula to do this automatically but this is
where it will get much more complicated and the length of the formula will
grow dramatically to the point where it's no better than your original
formula.

If you want to go that route let me know and I'll tweak it.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko!
You are awesome!!! I got this to work when I copied down the first column
within the same name but I am having difficulty pulling it across rows as
the
"names" change. Any thoughts??? Thanks!
--
-CRM


"T. Valko" wrote:

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file
that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a
value
for each time period therefore resulting in a 1 as the result. Do we
need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer
inspection
this won't be that complicated but the formula will be kind of long
but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I am reposting this discussion on a question I have been working on
for
several days below. It seems to be quite a challenge. Just seeing
if
anyone
else had input. I know it is generally frowned upon to report the
same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is
that
originally the time ins and time outs started in column E. We had
to
add
one
more T.I./T.O. set so they got put in columns C & D. I inherited
this
worksheet so I am having difficulty editing it. I thought perhaps
if
you
had
the original formula before the added time columns, it might help
you
or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure
it
out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll
play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Valko,
Thanks for the response and thanks for the formula correction.
I
was
testing an If stmt on the formula and forgot to take off the
last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I.
T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has
15
min
increments down column A and B. The formula needs to calculate
the
amount
of
time each individual was clocked in/out from the earlier table
over
each
15
min timeframe.

A B C D E .....
R
Sally Ben Chris
Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15
min
increments between 8 and 9 will read 0.25....then she retrurned
at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00
p
should
read "0" or i have it formatted to "-". This time in and time
outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need
to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in
cells
C10
and
D10....I need the formula to continue for time in/outs in
cells
E10:J10
and
then allocate to the correct 15 minute increment time
frame....i
hope
this
makes sense...let me know if you need more info.....THANKS
FOR
ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote
in
message
...
T.
Thanks for the response...see my response to Sean for more
detail....I
need
it to calculate in a decimal representation of an
hour...e.g.
1/2
hour
=
.5

"T. Valko" wrote:

I'm not sure what you're trying to do with this.

Is this what you want:

Time in: 7:30 AM
Time out: 8:27 AM

7:30...7:45...15:00
7:45...8:00...15:00
8:00...8:15...15:00
8:15...8:30...12:00
8:30...8:45
8:45...9:00

Assume your list of times in 15 minute increments is in the
range
A1:B6

G1 = time in = 7:30 AM
H1 = time out = 8:27 AM

Enter this formula in C1 and copy down to C6:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))

Format as m:ss

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote
in
message
...
I have a formula that calculates the amount of time
elapsed
of
15
minute
increments...The formula is part of a grid that looks
like
this:
7:30 a 7:45 a
7:45 a 8:00 a
etc. until

Valko,
Not necessary. I came to the conclusion myself and I went through and hand
edited each formula. It wasn't too bad since there were only 16 columns.
Once I got the heading formula correct I could copy down into the 40 or so
cells below it.

I can't tell you how much I appreciate your help! I would have spent weeks
figuring this out. Keep up the great work with helping all of us lost Excel
souls out there!
--
-CRM


"T. Valko" wrote:

That's where the real complication comes into play.

I used the same formula for each name and then manually changed the row
references. You could write the formula to do this automatically but this is
where it will get much more complicated and the length of the formula will
grow dramatically to the point where it's no better than your original
formula.

If you want to go that route let me know and I'll tweak it.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko!
You are awesome!!! I got this to work when I copied down the first column
within the same name but I am having difficulty pulling it across rows as
the
"names" change. Any thoughts??? Thanks!
--
-CRM


"T. Valko" wrote:

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file
that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns a
value
for each time period therefore resulting in a 1 as the result. Do we
need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer
inspection
this won't be that complicated but the formula will be kind of long
but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I am reposting this discussion on a question I have been working on
for
several days below. It seems to be quite a challenge. Just seeing
if
anyone
else had input. I know it is generally frowned upon to report the
same
information, I am just desperate to get this resolved. Any help is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue is
that
originally the time ins and time outs started in column E. We had
to
add
one
more T.I./T.O. set so they got put in columns C & D. I inherited
this
worksheet so I am having difficulty editing it. I thought perhaps
if
you
had
the original formula before the added time columns, it might help
you
or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can figure
it
out
it
won't be until tomorrow. I'm getting ready to call it a day. I'll
play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Valko,
Thanks for the response and thanks for the formula correction.
I
was
testing an If stmt on the formula and forgot to take off the
last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and time
outs(T.O.)
per individual

C D E F G
H
I J
T.I. T.O. T.I. T.O. T.I.
T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and has
15
min
increments down column A and B. The formula needs to calculate
the
amount
of
time each individual was clocked in/out from the earlier table
over
each
15
min timeframe.

A B C D E .....
R
Sally Ben Chris
Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the 15
min
increments between 8 and 9 will read 0.25....then she retrurned
at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and 1:00
p
should
read "0" or i have it formatted to "-". This time in and time
outs
can
happen 4 times in the day so having the incremental calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i need
to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Ok, so the background is below....i have edited the proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered in
cells
C10
and
D10....I need the formula to continue for time in/outs in
cells
E10:J10
and
then allocate to the correct 15 minute increment time
frame....i
hope
this
makes sense...let me know if you need more info.....THANKS
FOR
ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote
in
message
...

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Not necessary. I came to the conclusion myself and I went through and
hand
edited each formula. It wasn't too bad since there were only 16 columns.
Once I got the heading formula correct I could copy down into the 40 or so
cells below it.

I can't tell you how much I appreciate your help! I would have spent
weeks
figuring this out. Keep up the great work with helping all of us lost
Excel
souls out there!
--
-CRM


"T. Valko" wrote:

That's where the real complication comes into play.

I used the same formula for each name and then manually changed the row
references. You could write the formula to do this automatically but this
is
where it will get much more complicated and the length of the formula
will
grow dramatically to the point where it's no better than your original
formula.

If you want to go that route let me know and I'll tweak it.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko!
You are awesome!!! I got this to work when I copied down the first
column
within the same name but I am having difficulty pulling it across rows
as
the
"names" change. Any thoughts??? Thanks!
--
-CRM


"T. Valko" wrote:

You have to account for any empty cells in the Time In/Time Out range.

That makes the formula a little bit longer. Here's a small sample file
that
demonstrates this.

xTime.xls 17kb

http://cjoint.com/?bnfbSoOJc7

I've also used rounding to 2 decimal places.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in message
...
Valko,
Thanks for the reply. Whenever I evaluate this formula, it returns
a
value
for each time period therefore resulting in a 1 as the result. Do
we
need
some if statements to qualify each segment of the formula?
--
-CRM


"T. Valko" wrote:

Actually, we can reduce that further to:

=(MAX(0,MIN(TO1,B55)-MAX(TI1,A55))+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55)))/60*1440


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I haven't worked out the exact formula just yet but on closer
inspection
this won't be that complicated but the formula will be kind of
long
but
should not be as long as your original formula.

It's just a matter of stringing together 4 of these:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440

1 for each of the possible TI/TO's.

Like this:

=MAX(0,MIN(TO1,B55)-MAX(TI1,A55))/60*1440+
MAX(0,MIN(TO2,B55)-MAX(TI2,A55))/60*1440+
MAX(0,MIN(TO3,B55)-MAX(TI3,A55))/60*1440+
MAX(0,MIN(TO4,B55)-MAX(TI4,A55))/60*1440

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
I am reposting this discussion on a question I have been working
on
for
several days below. It seems to be quite a challenge. Just
seeing
if
anyone
else had input. I know it is generally frowned upon to report
the
same
information, I am just desperate to get this resolved. Any help
is
appreciated!
-CRM

Valko,
Here is the original formula below in cell C43. The only issue
is
that
originally the time ins and time outs started in column E. We
had
to
add
one
more T.I./T.O. set so they got put in columns C & D. I
inherited
this
worksheet so I am having difficulty editing it. I thought
perhaps
if
you
had
the original formula before the added time columns, it might
help
you
or
someone else find a resolution quicker.
Thanks!
-CRM

=IF($F$10($A43),IF($E$10$B43,0,IF($A43=$E$10,IF ($F$10<$B43,($F$10-$B43)*1440/60,0.25),IF($F$10$A43,($B43-$E$10)*1440/60))),0)+IF($H$10($A43),IF($G$10$B43,0,IF($A43= $G$10,IF($H$10<$B43,($H$10-$A43)*1440/60,0.25),IF($H$10$B43,($B43-$G$10)*1440/60))),0)+IF($J$10($A43),IF($I$10$B43,0,IF($A43= $I$10,IF($J$10<$B43,($J$10-$A43)*1440/60,0.25),IF($J$10$B43,($B43-$I$10)*1440/60))),0)

--
-CRM


"T. Valko" wrote:

Wow!

That's going to be incredibly complicated to do. If I can
figure
it
out
it
won't be until tomorrow. I'm getting ready to call it a day.
I'll
play
around with it tomorrow.

--
Biff
Microsoft Excel MVP


"saltnsnails" wrote in
message
...
Valko,
Thanks for the response and thanks for the formula
correction.
I
was
testing an If stmt on the formula and forgot to take off the
last
argument
before I pasted it into window.
Anyway....
The formula is sourcing from a table of time in(T.I.) and
time
outs(T.O.)
per individual

C D E F
G
H
I J
T.I. T.O. T.I. T.O.
T.I.
T.O.
T.I. T.O.
10 Sally
11 Ben
12 Chris
....
25 Stan

Then I have a table that transposes the name to columns and
has
15
min
increments down column A and B. The formula needs to
calculate
the
amount
of
time each individual was clocked in/out from the earlier
table
over
each
15
min timeframe.

A B C D E .....
R
Sally Ben Chris
Stan

7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
...... ........
4:45 p 5:00p

So Sally may have come in at 8:00 a left at 9:00a so all the
15
min
increments between 8 and 9 will read 0.25....then she
retrurned
at
1:00 p
and
left again at 3:00 p...so the increments between 9:00 a and
1:00
p
should
read "0" or i have it formatted to "-". This time in and
time
outs
can
happen 4 times in the day so having the incremental
calculations
knowing
when
time was worked and for how long is my difficulty.

Thanks for your patience and assistance...let me know if i
need
to
explain
further.
--
-CRM


"T. Valko" wrote:

What cells are to be compared to E10:J10?

Where are these formulas entered?

Describe your layout?

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

Should be:

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440


--
Biff
Microsoft Excel MVP


"saltnsnails" wrote
in
message
...
Ok, so the background is below....i have edited the
proposed
formula
with
my
cell references....

=MAX(0,MIN(D$10,B55)-MAX(C$10,A55))/60*1440,0)

and yes it does work...but only if times are only entered
in
cells
C10
and
D10....I need the formula to continue for time in/outs in
cells
E10:J10
and
then allocate to the correct 15 minute increment time
frame....i
hope
this
makes sense...let me know if you need more info.....THANKS
FOR
ANY
HELP!!!
-crm



OK, try this:

=MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440

You might want to round to 2 decimal places:

=ROUND(MAX(0,MIN(H$1,B1)-MAX(G$1,A1))/60*1440,2)

--
Biff
Microsoft Excel MVP


"saltnsnails"
wrote
in
message
...