# How to forecast time conditionally?

#### xlmaniac

##### Well-known Member
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

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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")

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

Replies
1
Views
569
Replies
11
Views
375
Replies
13
Views
565
Replies
5
Views
227
Replies
10
Views
250

Threads
1,203,327
Messages
6,054,751
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back