How to forecast pick-up date based on multiple conditions?

[xlmaniac]

Dear All,
We dispatch material via courier from our DC based on the orders received from the customers through internet. The DC operates from Monday to Saturday(Sunday being weekly off). Courier company also picks up material from our DC only from Monday to Saturday(Sunday being weekly off).
Apart from weekly off on Sunday, there are certain HOLIDAYs for both DC & courier company when the dispatch is not made.
There is a cut off time for the order processing for a particular day. If the customer has placed the order on or before 16PM then the dispatch will be made on the same day or else it will be dispatched on the next working day.
I am looking for a formula which can forecast the proposed pick-up date from the DC based on the following conditions:-
1)Formula to check whether the order is within the cut-off-time or not.
2)Formula to first check the list of weekly Offs(SUNNDAYS).
3)Formula then to check the HOLIDAY list of DC & HOLIDAY list of courier company.
Post checking the above conditions the formula needs to forecast a PICK-UP date accordingly.
I am providing a sample data with the desired result.
Would be of great help if somebody helps out with a solution.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Customer Order Date	Customer Order Time	Proposed Dispatch Date(Desired Result)	Remarks		List Of Weekly Offs(Both DC & Courier)	DC Holidays	Courier Holidays
2	4-Apr	14:20	4-Apr	Order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on 4th April.		1-Apr	6-Apr	6-Apr
3	5-Apr	13:35	7-Apr	6th April is DC HOLIDAY & hence the dispatch date is 7th April.		8-Apr	14-Apr	13-Apr
4	7-Apr	12:10	9-Apr	8th April is SUNDAY & hence the dispatch is on 9th April.		15-Apr	16-Apr
5	12-Apr	11:40	17-Apr	13th/14th/16th is HOLIDAY & 15th is SUNDAY & hence the dispatch is on 17th April.		22-Apr
6	15-Apr	22:50	17-Apr	15th is SUNDAY & 16th is HOLIDAY & hence the dispatch will be on 17th April.		29-Apr
7	20-Apr	20:40	21-Apr	Order time is after the Cut-Off Time of 16PM and hence the dispatch will be done on 21st April.
8	21-Apr	23:15	23-Apr	Order time is after the Cut-Off Time of 16PM & 22nd April is SUNDAY and hence the dispatch will be done on 23rd April.
Sheet3

 

[xlmaniac]

Hi Cyrilbrd,
Sorry to bother you again.
My mistake that I over-looked the result for the 5th scenario suggested by you.
This is for 24th Dec(Order Time-22:50).The pick-up date needs to be 26th Dec and not 27th Dec(since 25th Dec is holiday).
Thanks again

 

[cyrilbrd]

Ok,
I would like to suggest the simplest approach.
Create a database DB where the dates of interest will be placed.
In A1 "status"
In B1 "date"
in C1 "newdate"
and from B2 to B367 the complete dates of 2012 starting 1st of Jan in B2 till 31st of Dec in B367.
Not much to this yet...
In Column A, just write "VL" in front of each date that will be either a sunday, or a national Holiday and other when you are sure that either your company or the Carrier would not work. that was provided by you. No worries I prepared the list for you...

In Column C we will place the date that should appear if ever the date in B is a Holiday.

This allows extreme flexibility, your government or boss or carrier may decide in advance that a "supposed to be working day" will be a day Off, just just have to add "VL" in column A corresponding to the said date and correct the value in Column C and the formula will do the rest.

In order to simply the ranges I used Named Ranges. Column B is "date" and Column C is "newdate".

In cell D1 type: "COT is"
in Cell E1 type: "16:30" (4:30:00 PM) and Named Range as "COT"

Now go to the Worksheet containing your data.
Column A is "Customer Order Date" right? with the dates below.
Column B is "Customer Order Time" with the time.
Column C is "Proposed Dispatch Date".
Cell C2 is:

=IF(B2>COT,INDEX(newdate,MATCH(A2+1,date,0)),INDEX(newdate,MATCH(A2,date,0)))

but I suggest using:

=IF(ISNA(IF(B2>COT,INDEX(newdate,MATCH(A2+1,date,0)),INDEX(newdate,MATCH(A2,date,0)))),"",IF(B2>COT,INDEX(newdate,MATCH(A2+1,date,0)),INDEX(newdate,MATCH(A2,date,0))))

because it will return a blank in lieu of a #N/A when there is no date in column A.

Copy down till needed.

please advise if ok.

link to your file.

 

[cyrilbrd]

Hi Cyrilbrd,
Sorry to bother you again.
My mistake that I over-looked the result for the 5th scenario suggested by you.
This is for 24th Dec(Order Time-22:50).The pick-up date needs to be 26th Dec and not 27th Dec(since 25th Dec is holiday).
Thanks again

Noted but as per the list you gave me your Courrier will be on leave on the 26th...

Holiday List-DC Holiday List-Courier
26-Jan 26-Dec
8-Mar 20-Feb
6-Apr 8-Mar
1-May 9-Mar
15-Aug 13-Apr
20-Aug 14-Apr
2-Oct 10-Aug
22-Oct 15-Aug
23-Oct 2-Oct
24-Oct 24-Oct
13-Nov 27-Oct
25-Dec 13-Nov
14-Nov

So the next open day is 27th of December.
Right?

 

[cyrilbrd]

xlmaniac, if the date "26 - Dec" in "Holiday List-Courier" is a typo, and should have been "26 - jan".

Then just change DB Cell A362 and delete the VL, the cell won't be highlighted, then in C361 and C362 change "27-Dec" into "26-Dec".
That will be all.

so it will show:

24-Dec	11:40	24-Dec
24-Dec	22:50	26-Dec

Please advise if this is correct. (do you need me to send the new file with those corrections?)

Hi XLSM, your approach is quite good but being lazy (one of my qualities...) I opted for a faster and less demanding solution. The disadvantage of my solution is the existence of the DB (database), however DB are quite the simplest solution and allow users to review their bases and modify them whenever needed without having to change the formulas.

Cheers!.

 

[XLSM]

Cyrilbrd,

I agree that using a db makes it easier for the user to 'maintain' this solution. Especially if the user is not the one writing the functions that i had used in my solution.

I appreciate you comparing our solutions and listing the pros and the cons.

Cheers!

 

[xlmaniac]

Hi Cyrilbrd,
I am really overwhelmed and touched with your empathy towards the solution of my problem.

I can not only say thank you to you because what you have done is really a WOW factor to me and I will be ever grateful & indebted to you for this amazing & overwhelming solution that you have provided.

Once again I would like to express my sincere gratitude to you for extending all the support & help sparing your valuable time for me.
You are right that 26th DEcember was a typo error.

Anyway I have corrected the file as advised by you.

I realy feel proud & priviledged to be a part of this great fraternity in MR.EXCEL and I also cherish the learning that I learn from all the EXCEL GURUS like you.

With Warm Regards

 

[cyrilbrd]

xlmaniac...

Cool,
Glad it worked for you!

Let us know if you have other puzzles to solve.
Bro, I am not a GURU, XLSM's solution is quite cool too, let's just say that I we are all learning from each others!

Cheers.

 

[xlmaniac]

Hi Cyrilbrd,
It is your magnanimity and modesty that you are claimimg yourself to be a learner.
Mr excel rocks and I am proud to have been associated with people like you who has got the empathy towards the solution of other's problem.
I will surely let you know if I come across any problem that is beyond my capabilities to solve.
Anyway my best wishes to you and I am sure that you will continue helping excel lovers like us.
Thanks once again....