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

 

[cyrilbrd]

Hi,

Why is the rule "Order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on the same day" not applied to all? Is there another factor to take into consideration that would overrule the Cut-Off time and move the dispatch to the next day?

 

[xlmaniac]

Hi,
Thanks for your reply & help.
The CUT-OFF-TIME concept is made since it takes 1-2 hours for order processing & packing and the courier company collects the shipment latest by 6.30 PM.
There is no other issues.

 

[cyrilbrd]

Ok noted so any order prior to 1630 should be dispatched on the same day right?
So I assume that the samples you placed are just to show the possibilities and I shouldn't pay too much attention to the time for those but rather find a way to include the dates.

Is this correct?

Do you have a list of declared Holidays in your country?
The Sundays can be determined with a formula.
The list could be used as a database.

 

[xlmaniac]

Hi,
You are right.
I Have provided with some scenarios for better understanding.
Thanks

 

[cyrilbrd]

Ok,
the sundays for 2012 are:

1/1/12
1/8/12
1/15/12
1/22/12
1/29/12
2/5/12
2/12/12
2/19/12
2/26/12
3/4/12
3/11/12
3/18/12
3/25/12
4/1/12
4/8/12
4/15/12
4/22/12
4/29/12
5/6/12
5/13/12
5/20/12
5/27/12
6/3/12
6/10/12
6/17/12
6/24/12
7/1/12
7/8/12
7/15/12
7/22/12
7/29/12
8/5/12
8/12/12
8/19/12
8/26/12
9/2/12
9/9/12
9/16/12
9/23/12
9/30/12
10/7/12
10/14/12
10/21/12
10/28/12
11/4/12
11/11/12
11/18/12
11/25/12
12/2/12
12/9/12
12/16/12
12/23/12
12/30/12

Right?

can you provide the date when you will have national Holidays and other when you are sure that either You or the Carrier would not work?

 

[xlmaniac]

Hi,
Thanks for all your suport & help.
Really appreciate the same.
The following is the list of the holidays for 2012:-
<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=232><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=101>Holiday List-DC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=131>Holiday List-Courier</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>26-Jan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>26-Dec</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>8-Mar</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>20-Feb</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>6-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>8-Mar</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>1-May</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>9-Mar</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>15-Aug</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>13-Apr</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>20-Aug</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>14-Apr</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>2-Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10-Aug</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>22-Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>15-Aug</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>23-Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2-Oct</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>24-Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>24-Oct</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>13-Nov</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>27-Oct</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>25-Dec</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>13-Nov</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>14-Nov</TD></TR></TBODY></TABLE>
Regards

 

[XLSM]

The easiest way that I was able to think of was to have 5 iterations of dates in separate columns and then take the max of the dates in these five columns.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The first iterative value (column I in the image below) is based on the date given in column A, i.e. the customer order date. This checks for the time cutoff, weekly holiday and respective holidays for DC as well as the courier. The function for this iterative value is given below.
<o></o>

IF(ISERROR(MATCH(IF(ISERROR(MATCH(IF(WEEKDAY(IF($B3>$C3,$A3+I$1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),DCHolidays,0)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3))+1),CourierHolidays,0)),IF(ISERROR(MATCH(IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),DCHolidays,0)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3))+1),IF(ISERROR(MATCH(IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),DCHolidays,0)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3)),IF(WEEKDAY(IF($B3>$C3,$A3+1,$A3),1)=1,IF($B3>$C3,$A3+1,$A3)+1,IF($B3>$C3,$A3+1,$A3))+1)+1)

<TABLE style="WIDTH: 861pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1145><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 197pt; mso-width-source: userset; mso-width-alt: 9618" width=263><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=61><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=3 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20 width=79>Column --></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=62>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=106>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 197pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=263>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=62>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61>G</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61>H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=68>I</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=62>J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=64>K</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=64>L</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=64>M</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=68>N</TD></TR><TR style="HEIGHT: 64.5pt" height=86><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 59pt; HEIGHT: 64.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=86 width=79>Customer Order Date


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=61>Customer Order Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=62>Order Processing CUT-OFF-TIME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 80pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 width=106>Proposed Dispatch Date(Desired Result)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffcc99; WIDTH: 197pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83 width=263>Explanation For The Proposed Dispatch Date(Desired Result)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=62>List Of Weekly Offs(Both DC & Courier)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=61>DC Holidays</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=61>Courier Holidays</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 51pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=68>1st Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=62>2nd Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=64>3rd Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=64>4th Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=64>5th Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>Final date</TD></TR><TR style="HEIGHT: 51.75pt" height=69><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 59pt; HEIGHT: 51.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl76 height=69 width=79>4-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=61>14:20</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=62>16:00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: yellow; WIDTH: 80pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl78 width=106>4-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ffcc99; WIDTH: 197pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=263>4th April is a working day & order time is before the Cut-Off Time of 16PM and hence the dispatch will be done on 4th April.</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=62>1-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=61>6-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79 width=61>6-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 51pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=68>4-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 47pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=62>4-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=64>4-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=64>4-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80 width=64>4-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 align=right>4-Apr</TD></TR><TR style="HEIGHT: 51.75pt" height=69><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 59pt; HEIGHT: 51.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=69 width=79>5-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=61>19:30</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=62>16:00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: yellow; WIDTH: 80pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=106>7-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ffcc99; WIDTH: 197pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=263>Order is received after CUT-OFF-TIME of 16PM on 5th April & 6th April is DC HOLIDAY & hence the dispatch date is 7th April.</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=62>8-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=61>14-Apr</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 46pt; BORDER-TOP: black; BORDER-RIGHT: #e0dfe3" class=xl70 width=61>13-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 51pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=68>7-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=62>7-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>7-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>7-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=64>7-Apr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 align=right>7-Apr</TD></TR></TBODY></TABLE>

Before you add these functions, you will have to create two named ranges called DCHolidays and CourierHolidays in the same workbook. Each of these ranges are one column wide and should contain ALL the dates marked as holidays for both these entities.

<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=75><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 39.75pt" height=53><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 39.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=53 width=75>Holiday List-DC


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>1/26/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>3/8/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>4/6/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>5/1/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>8/15/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>8/20/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>10/2/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>10/22/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>10/23/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>10/24/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>11/13/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=75 align=right>12/25/2012</TD></TR>


<TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 56pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_3122171 class=xl67 height=21 width=75 align=right>4/16/2012</TD></TR>
</TBODY></TABLE><o></o><o></o>

<TABLE style="WIDTH: 55pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=73><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 39.75pt" height=53><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 39.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=53 width=73>Holiday List-Courier</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>2/20/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>3/8/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>3/9/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>4/13/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>4/14/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>8/10/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>8/15/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>10/2/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>10/24/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>10/27/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>11/13/2012</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 height=21 width=73 align=right>11/14/2012


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_3122171 class=xl67 height=21 width=73 align=right>12/26/2012</TD></TR></TBODY></TABLE><o></o>
The second iterative value is based off the 1st iterative value. What I am doing here is just checking for the weekly holiday, DC and and courier holidays. I am not checking for Sunday here as I think that it will be taken care off in the first step itself. This is being done in column J of the image below. The function that I am using for this is <o></o>
<o></o>

=IF(ISERROR(MATCH(IF(ISERROR(MATCH(IF(WEEKDAY($I3)=1,$I3+1,$I3),DCHolidays,0)),IF(WEEKDAY($I3,1)=1,$I3+1,$I3),IF(WEEKDAY($I3,1)=1,$I3+1,$I3)+1),CourierHolidays,0)),IF(ISERROR(MATCH(IF(WEEKDAY($I3,1)=1,$I3+1,$I3),DCHolidays,0)),IF(WEEKDAY($I3,1)=1,$I3+1,$I3),IF(WEEKDAY($I3,1)=1,$I3+1,$I3)+1),IF(ISERROR(MATCH(IF(WEEKDAY($I3,1)=1,$I3+1,$I3),DCHolidays,0)),IF(WEEKDAY($I3,1)=1,$I3+1,$I3),IF(WEEKDAY($I3,1)=1,$I3+1,$I3)+1)+1)

<o></o>
<o></o>
Similarly, I have three more iterations, each based off the date calculated in the preceding iteration.<o></o>
<o></o>
The function for the 3rd iteration is <o></o>
<o></o>

=IF(ISERROR(MATCH(IF(ISERROR(MATCH(IF(WEEKDAY($J3)=1,$J3+1,$J3),DCHolidays,0)),IF(WEEKDAY($J3,1)=1,$J3+1,$J3),IF(WEEKDAY($J3,1)=1,$J3+1,$J3)+1),CourierHolidays,0)),IF(ISERROR(MATCH(IF(WEEKDAY($J3,1)=1,$J3+1,$J3),DCHolidays,0)),IF(WEEKDAY($J3,1)=1,$J3+1,$J3),IF(WEEKDAY($J3,1)=1,$J3+1,$J3)+1),IF(ISERROR(MATCH(IF(WEEKDAY($J3,1)=1,$J3+1,$J3),DCHolidays,0)),IF(WEEKDAY($J3,1)=1,$J3+1,$J3),IF(WEEKDAY($J3,1)=1,$J3+1,$J3)+1)+1)

<o></o>
<o></o>
Function for 4th iteration<o></o>
<o></o>

=IF(ISERROR(MATCH(IF(ISERROR(MATCH(IF(WEEKDAY($K3)=1,$K3+1,$K3),DCHolidays,0)),IF(WEEKDAY($K3,1)=1,$K3+1,$K3),IF(WEEKDAY($K3,1)=1,$K3+1,$K3)+1),CourierHolidays,0)),IF(ISERROR(MATCH(IF(WEEKDAY($K3,1)=1,$K3+1,$K3),DCHolidays,0)),IF(WEEKDAY($K3,1)=1,$K3+1,$K3),IF(WEEKDAY($K3,1)=1,$K3+1,$K3)+1),IF(ISERROR(MATCH(IF(WEEKDAY($K3,1)=1,$K3+1,$K3),DCHolidays,0)),IF(WEEKDAY($K3,1)=1,$K3+1,$K3),IF(WEEKDAY($K3,1)=1,$K3+1,$K3)+1)+1)

<o></o>
<o></o>
Function for 5th iteration<o></o>
<o></o>

=IF(ISERROR(MATCH(IF(ISERROR(MATCH(IF(WEEKDAY($L3)=1,$L3+1,$L3),DCHolidays,0)),IF(WEEKDAY($L3,1)=1,$L3+1,$L3),IF(WEEKDAY($L3,1)=1,$L3+1,$L3)+1),CourierHolidays,0)),IF(ISERROR(MATCH(IF(WEEKDAY($L3,1)=1,$L3+1,$L3),DCHolidays,0)),IF(WEEKDAY($L3,1)=1,$L3+1,$L3),IF(WEEKDAY($L3,1)=1,$L3+1,$L3)+1),IF(ISERROR(MATCH(IF(WEEKDAY($L3,1)=1,$L3+1,$L3),DCHolidays,0)),IF(WEEKDAY($L3,1)=1,$L3+1,$L3),IF(WEEKDAY($L3,1)=1,$L3+1,$L3)+1)+1)

<o></o>
<o></o>
<o></o>
Once, i have calcualted these five values, I just take a max of them and the resultant value is the forecasted date that you are looking for. The reason for using five iterations is that looking at your list of holidays, I noticed that max number of consecutive holidays is 3. Add another day for a Sunday as well as a day for accomodating the rule about daily cutoffs and we get a total of 5 days. So, I think 5 iterations should take care of all your scenarios. <o></o>
<o></o>
Having said this, there were a couple of scenarios (rows 8 & 9) where my projected value was not in sync with your desired value. This is because you dont have 4/16 in the list of holidays. Once I added this to the list, these scenarios where working as expected as well.<o></o>
<o></o>
I think that UDFs could be an alternative as well, but I am not very well versed with them, so did not try setting that up for you. Hope this solves your problem.<o></o>
<o></o>

 

[cyrilbrd]

For you appraisal:

4-Apr	14:20	4-Apr
5-Apr	13:35	5-Apr
7-Apr	12:10	7-Apr
24-Dec	11:40	24-Dec
24-Dec	22:50	27-Dec
20-Oct	8:40	20-Oct
20-Oct	23:15	25-Oct

As stated:
IF the Cut-Off time is inferior to 16:30 then the dispatch should be on the same day.
IF the Cut-Off time is superior to 16:30 then the dispatch will be moved to the next opened day.

I took back your example but this time I took into consideration both the date AND the time. So for the first three (3) lines since the Order Time is inferior to 16:30 and the date is a working day then the delivery is set on the same day.

I also placed two examples, one set on the 24th of december and one on the 20th of October, both with different cut off times.

Let me know if this is what you need.

 

[xlmaniac]

Hi Cyrilbrd,
Yes you are absolutely right.
This is what I want.
Thanks for all your time and input.
Really appreciate the same.