How to forecast time conditionally?

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
505
Office Version
  1. 2010
Platform
  1. Windows
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

*ABCD
1Order Receipt TimeProcess Start Time(Desired Result)Result(With Formula)Remarks
2PM 05:35:00AM 09:30:00PM 06:35:00Formula Giving Incorrect Result.
3PM 07:40:00AM 09:30:00PM 08:40:00Formula Giving Incorrect Result.
4AM 01:15:00AM 09:30:00AM 02:15:00Formula Giving Incorrect Result.
5AM 04:50:00AM 09:30:00AM 05:50:00Formula Giving Incorrect Result.
6AM 08:30:00AM 09:30:00AM 09:30:00Formula Giving Correct Result.
7AM 10:30:00AM 11:30:00AM 11:30:00Formula Giving Correct Result.
8PM 01:32:00PM 02:32:00PM 02:32:00Formula Giving Correct Result.
9PM 03:30:00PM 04:30:00PM 04:30:00Formula 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
CellFormula
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,785
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 2, 2009
Messages
505
Office Version
  1. 2010
Platform
  1. Windows
Dear Sir,
Completely amazing & mesmerising sollution!!:)
Thank you again for helping me out.
Really appreciate the same.
Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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

Which adblocker are you using?

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
Top