Help with a formula I am editing

[saltnsnails]

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

 

[Sean Timmons]

This seems like way too much formula for what you are trying to do. Could you
give just a sample of what columns A - F would be for an individual row?

Looks like you're trying to validate that each time is in order, otherwise
0.. but not quite sure on that.

 

[T. Valko]

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

 

[saltnsnails]

OK, the block of cells C10:K25 is devoted to "time in" and "time out" manual
inputs. Columns C,E,G, and I are Time ins and D,F,H,J are time outs. Very
basic time. Cells A10-B25 is devoted to employee names.
C D E F
Time in Time Out Time In Time Out
10 Name

11 Name

12 Name

Name

Below that I have a whole table from C55:R92 that has each row divided into
15 min increments (which are reflected in columns A & B).

For example:
A B Name Name Name Name Name
(names are auto filled from time in, time out table)
7:30 a 7:45 a
7:45 a 8:00 a
8:00 a 8:15 a
etc until
4:45 p 5:00 p

So basically I already had the formula below but i had to add one more set
of time in and time outs.....I added them to the front of the time in/time
out section b/c I had some free columns where I took out other numbers I
didnt need (like employee # etc)....so columns C & D need to be added to the
formulas in the 15 min increment section.

Basically all the formula does is take the 15 min increment and find that
time frame in the time in/out section and records how much of that increment
the employee worked. So if an employee worked from 8:30a - 9:30 a, it will
it should record 4 successive blocks of 0.25 hours totaling an hour. If the
employee only worked a fraction of the 15 minute increment, the formula will
calculate a decimal representing the fraction of an hour, for example 9:00 a
to 9:10 a would be 0.17 of an hour.

I hope this makes more sense...
thanks!

 

[saltnsnails]

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]

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)