Help - determine date base on multiple criteria

[dstepan]

I am using Excel 2003.
I am trying to create a formula that is dependent on several factors...

1. Scheduled Date (this will never be a Sat or Sun)
2. Scheduled Date minus 2 (week)days = Arrival date. The arrival date must be a weekday and cannot be 9/3 or 9/6.

For Example -
Scheduled Date: 9/7 (Tuesday)
Arrival Date : 9/2 (Thursday)
The arrival date cannot be Saturday or Sunday. The arrival date cannot not be 9/3 or 9/6.
The arrival date should be 9/2/10 which is a weekday and not 9/3 or 9/6.

So far my formula to determine the arrival date consists of 13 columns and multiple "IF" functions.
Ultimately I would like to have my answer in one step. If the scheduled date is known (ae2) then I am looking for a formula that can determine what date my arrival date should be if the arrival date is at least 2 weekdays before the scheduled date and is not 9/3/10 or 9/6/10.
I was hoping someone could provide a more succint formula
ae2 = 9/7/10 [date a vlookup formula]
ah2= 9/5/10 [a1 - 2]
ai2 = 7 [weekday(a2,2)]
aj2 = Sun [IF(c1=1,"Mon",IF(c1=2,"Tue",IF(c1=3,"Wed",IF(c1=4,"Thu",IF(c1=5,"Fri",IF(c1=6,"Sat","Sun"))))))]
ak2 = 9/3/10
al2 = 9/6/10
am2 = 9/3/10 [IF(AH2=AK2,AT2,IF(AH2=AL2,AT2,IF(AJ2="sun",AE2-4,IF(AJ2="sat",AE2-3,IF(AE2>1,AE2-2," "))))))]
an2 = 5 [WEEKDAY(AM2,2)]
ao2 = Fri [IF(AN2=1,"Mon",IF(AN2=2,"Tue",IF(AN2=3,"Wed",IF(AN2=4,"Thu",IF(AN2=5,"Fri",IF(AN2=6,"Sat","Sun"))))))]

 

[milesUK]

I think this might suffice.
In cell AF2 place

=AE2-IF(AE2=40428,5,IF(AE2=40429,6,IF(OR(WEEKDAY(AE2)=2,WEEKDAY(AE2)=3),4,2)))

I could not get the IF to recognise 9/7/10 or 7/9/10 hence the hard numbers.

 

[milesUK]

I should have searched the net for help first! Seems that dates are best utilised with the DATE function:

=AE2-IF(AE2=DATE(2010,9,7),5,IF(AE2=DATE(2010,9,8),6,IF(OR(WEEKDAY(AE2)=2,WEEKDAY(AE2)=3),4,2)))