time formula question

D

David

This formula works great if the ending time is before 0:00.

=IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)*
{2;3;4})),"")

$C$9:$C$11 is my starting time i.e. 20:00
$E$9:$E$11 is my ending time i.e. 04:30

How can I get this to work if A21 = 20:15?


TIA,
David
 
A

Ardus Petus

Try this:
=IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"")

HTH
 
D

David

Ardus said:
Improved:
=IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX(IF($C$9:$C$11<$E$9:$E$11;($A21>=$C$9:$C$11)*($A21<=$E$9:$E$11);($A21>=$C$9:$C$11)+($A21<=$E$9:$E$11))*{2;3;4})),"")

See example: http://cjoint.com/?gedbPtUDFH

HTH
Click to expand...

Thanks for replying Ardus. I tried what you had suggested but didn't
seem to get it to work. Here is my worksheet which will probably give
you a better idea than how I explained it.
http://home.rochester.rr.com/dfizer/

Thanks for taking the time,
David
 
A

Ardus Petus

There was a bug in my code whenever one of the 3 time slots is empty

Cheers,
 
Top