Help - determine date base on multiple criteria

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
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"))))))]
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
I think this might suffice.
In cell AF2 place

Code:
=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

Active Member
Joined
Jan 5, 2003
Messages
388
I should have searched the net for help first! Seems that dates are best utilised with the DATE function:

Code:
=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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top