Thanks Valko!
--
-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