How to forecast time conditionally?

[xlmaniac]

Dear All,
I am trying to forecast order processing start time based on the order receiving time at DC.
All orders received between 17:30 PM of any day till 9:30 AM of next day will get processed from 9:30 AM of next day.
For all other orders received between 9:30 AM-17:30 PM in a day will be processed within 1 hour from the receipt of the order.
I have tried to put a formula to forecast the time but it is yielding the correct result when the time is between 9:30-17:30 of any day and is yielding incorrect result for the time between 17:30 PM to 9:30 AM.
I am attaching the sample for your reference.
Could somebody help me with the correct formula pls?
Thanks

Sheet1

*	A	B	C	D
1	Order Receipt Time	Process Start Time(Desired Result)	Result(With Formula)	Remarks
2	PM 05:35:00	AM 09:30:00	PM 06:35:00	Formula Giving Incorrect Result.
3	PM 07:40:00	AM 09:30:00	PM 08:40:00	Formula Giving Incorrect Result.
4	AM 01:15:00	AM 09:30:00	AM 02:15:00	Formula Giving Incorrect Result.
5	AM 04:50:00	AM 09:30:00	AM 05:50:00	Formula Giving Incorrect Result.
6	AM 08:30:00	AM 09:30:00	AM 09:30:00	Formula Giving Correct Result.
7	AM 10:30:00	AM 11:30:00	AM 11:30:00	Formula Giving Correct Result.
8	PM 01:32:00	PM 02:32:00	PM 02:32:00	Formula Giving Correct Result.
9	PM 03:30:00	PM 04:30:00	PM 04:30:00	Formula Giving Correct Result.

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 129px;"><col style="width: 221px;"><col style="width: 142px;"><col style="width: 209px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(AND(A2>TIMEVALUE("17:30"),A2<=TIMEVALUE("9:30")),"9:30",A2+TIME(1,0,0)) C3 =IF(AND(A3>TIMEVALUE("17:30"),A3<=TIMEVALUE("9:30")),"9:30",A3+TIME(1,0,0)) C4 =IF(AND(A4>TIMEVALUE("17:30"),A4<=TIMEVALUE("9:30")),"9:30",A4+TIME(1,0,0)) C5 =IF(AND(A5>TIMEVALUE("17:30"),A5<=TIMEVALUE("9:30")),"9:30",A5+TIME(1,0,0)) C6 =IF(AND(A6>TIMEVALUE("17:30"),A6<=TIMEVALUE("9:30")),"9:30",A6+TIME(1,0,0)) C7 =IF(AND(A7>TIMEVALUE("17:30"),A7<=TIMEVALUE("9:30")),"9:30",A7+TIME(1,0,0)) C8 =IF(AND(A8>TIMEVALUE("17:30"),A8<=TIMEVALUE("9:30")),"9:30",A8+TIME(1,0,0)) C9 =IF(AND(A9>TIMEVALUE("17:30"),A9<=TIMEVALUE("9:30")),"9:30",A9+TIME(1,0,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[shg]

Here are two ways, in B2 and copy down:

=--CHOOSE(MATCH(A2, --{0,"9:30","17:30"}), "09:30", A2+"1:00", "9:30")

or

=--IF(MEDIAN("9:30", A2, "17:30") = A2, A2+"1:00", "9:30")

 

[xlmaniac]

Dear Sir,
Completely amazing & mesmerising sollution!!
Thank you again for helping me out.
Really appreciate the same.
Regards