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