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