Re: time formula question



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

Cheers,
--
AP

"David" <dfizer@xxxxxxxxxxxxxxxx> a écrit dans le message de news:
XfBgg.9953$3B.9944@xxxxxxxxxxxxxxxxxxxxxxx
Ardus Petus wrote:
Here is your corrected workbook: http://cjoint.com/?gejyaMc8zg

Cheers
--
AP

"David" <dfizer@xxxxxxxxxxxxxxxx> a écrit dans le message de news:
3Msgg.26531$8G3.20939@xxxxxxxxxxxxxxxxxxxxxxx
Ardus Petus wrote:
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
--
AP

"David" <dfizer@xxxxxxxxxxxxxxxx> a écrit dans le message de news:
1mhgg.8603$W97.3616@xxxxxxxxxxxxxxxxxxxxxxx
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

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


Thank you so much Ardus. What did I not do?

Thanks
Again
David


.