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