Excel Formula for New Date Calculation

[limeyadam]

Hi All,

I am working on an Excel forumla to calculate a schedule date for an order. It needs to take the date in column C and add 14 days to it. It also accounts for work holidays, by adjusting how many days are added to the original date.

That's the simple part.

Where I am having issues is when I want to complete a region specific adjustment.

Say the report looks like this:

<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=388 border=0><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=54 height=21>Region

​

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=81>ID Number

​

</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>Order Date

​

</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=96>Current Date

​

</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=74>New Date

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>1

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/5/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>1

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EFGH

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/6/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>2

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/7/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>2

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EFGH

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/8/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>3

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABCD

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/9/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>3

​

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EFGH

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/10/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/31/2011

​

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

​

</TD></TR></TBODY></TABLE>​

I want the formula to be placed in the 'New Date' column, and works as follows:

1. If 'Order Date' + 14 days is less than or equal to today, return 'OVERDUE'

2. If 'Region' = 2, and 'Order Date' is a weekend day, adjust so that 'New Date' is a Friday that is within 14 days of 'Order Date'

3. If 'Order Date' + 14 days is equal to a Holiday Day, adjust so that new date is a non-Holiday Day that is within 14 days of Order Date'

Here is what I have so far:

=IF((C2+14)<=TODAY(),"No Truck",
(IF(A2=9506,(IF(WEEKDAY(C2)=1,(C2+12),
(IF(WEEKDAY(C2)=7,(C2+13),
(IF(OR((C2+14)=DATEVALUE("12/25/2012"),(C2+14)=DATEVALUE("1/1/2013")),(C2+10),
(IF(OR((C2+14)=DATEVALUE("9/5/2011"),(C2+14)=DATEVALUE("1/6/2012"),(C2+14)=DATEVALUE("5/28/2012"),(C2+14)=DATEVALUE("9/3/2012"),(C2+14)=DATEVALUE("12/24/2012"),(C2+14)=DATEVALUE("12/31/2012")),(C2+11),
(IF(OR((C2+14)=DATEVALUE("12/25/2011"),(C2+14)=DATEVALUE("1/1/2012")),(C2+12),
(IF(OR((C2+14)=DATEVALUE("11/24/2011"),(C2+14)=DATEVALUE("12/24/2011"),(C2+14)=DATEVALUE("12/31/2011"),(C2+14)=DATEVALUE("7/4/2012")),(C2+13)))))))))))))),
(IF(OR((C2+14)=DATEVALUE("12/25/2011"),(C2+14)=DATEVALUE("1/1/2012"),(C2+14)=DATEVALUE("12/25/2012"),(C2+14)=DATEVALUE("1/1/2013")),(C2+12),
(IF(OR((C2+14)=DATEVALUE("9/5/2011"),(C2+14)=DATEVALUE("11/24/2011"),(C2+14)=DATEVALUE("12/24/2011"),(C2+14)=DATEVALUE("12/31/2011"),(C2+14)=DATEVALUE("1/16/2012"),(C2+14)=DATEVALUE("5/28/2012"),(C2+14)=DATEVALUE("7/4/2012"),(C2+14)=DATEVALUE("9/3/2012"),(C2+14)=DATEVALUE("12/24/2012"),(C2+14)=DATEVALUE("12/31/2012")),(C2+13),(C2+14)))))))

I keep getting an error value though.

Please assist!

Thank you!

 

[limeyadam]

I realized that I used some criteria that are specific to my report.

Please replace '9506' with Region '2', and 'No-Truck' with "OVERDUE'.

Thank you.

 

[pedro-egoli]

This has not solved your problem in one formula however, it may trigger another way to look at your problem .
It involves use of Weekday function being added to spreadsheet and if formulas in columns E,F and G can be combined this might be the answer.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Region	id no	order date	current date	SCENARIO 1	SCENARIO 2	SCENARIO 3	weekday
2	1	abcd	05-08-11	23-08-11	overdue	18-08-11	19-08-11	6
3	1	efgh	06-08-11	23-08-11	overdue	*	20-08-11	7
4	2	abcd	07-08-11	23-08-11	*	19-08-11	21-08-11	1
5	2	efgh	08-08-11	23-08-11	*	*	22-08-11	2
6	3	abcd	09-08-11	23-08-11	*	*	23-08-11	3
7	3	efgh	10-08-11	23-08-11	*	*	24-08-11	4
Sheet1

Pedro