# 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

.