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