Formula to find next Thursday's date

[TinaP]

I need a formula to find the delivery date for an order. If the order is placed by Friday, it will be delivered on the following Thursday as long as it isn't a holiday when it would be the following Friday. I have the holidays listed in a range of cells I2:I5.

<TABLE style="WIDTH: 101pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=135 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>Ordered</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Delivery</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/3/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/9/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/6/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/16/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/7/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/16/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/8/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/16/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/9/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/16/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4/10/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4/16/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>11/20/2009</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>11/27/2009</TD></TR></TBODY></TABLE>

Is it possible? If I can calculate the next Thursday part without taking into account the holidays, I'd be very happy.

 

[TinaP]

I think I found a formula that will work to find Thursday's delivery date since no one will be placing orders on the weekends:
=A1+12-WEEKDAY(A1)

Now I just have to figure out the holiday thing.

 

[Scott Huish]

=A1+CHOOSE(WEEKDAY(A1),4,3,2,1,7,6,5)+(ISNUMBER(MATCH(A1+CHOOSE(WEEKDAY(A1),4,3,2,1,7,6,5),$I$2:$I$5,0)))

Took a guess at your 2nd holiday

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	4/5/2009	4/9/2009
2	4/6/2009	4/9/2009							1/1/2009
3	4/7/2009	4/9/2009							7/3/2009
4	4/8/2009	4/9/2009							11/26/2009
5	4/9/2009	4/16/2009							12/24/2009
6	4/10/2009	4/16/2009
7	11/20/2009	11/27/2009
8
Sheet1

 

[TinaP]

Oh, excellent!

Eventually, I may have made it to your formula, but it would've taken weeks. I never thought about using the second parameter of the weekday function. And I know next to nothing about the choose function. I guess I have some studying to do...

 

[Scott Huish]

I didn't use the second parameter of the Weekday function, but glad it helped

 

[TinaP]

Yeah, I figured that out after studying the formula a bit. It's a creative formula. I have a lot to learn.

Thank you.

 

[Scott Huish]

Another possibility:

=A1+7-MOD(WEEKDAY(A1)-5,7)+(ISNUMBER(MATCH(A1+7-MOD(WEEKDAY(A1)-5,7),$I$2:$I$5,0)))

 

[TinaP]

OK, mod, I understand. These are slick formulas. Thank you.

 

[barry houdini]

Perhaps you could use WORKDAY function, i.e.

=WORKDAY(A1+11-WEEKDAY(A1),1,I$2:I$5)

 

[TinaP]

I'm creating this worksheet for people who only have Excel XP or 2003. They're in our branches and I don't want to have to explain how to install an add-in, so Workday is out of the question.