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