i need more help with this time question

[saltnsnails]

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)

 

[T. Valko]

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

 

[saltnsnails]

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

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

 

[T. Valko]

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

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.

 

[saltnsnails]

Thanks Valko!
--
-CRM

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.

 

[saltnsnails]

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)