Weekday formatting

[MRomanow]

Here is my problem:

I have an Order Date. I am trying to generate a Ship Date based on this Order Date. If the Order Date is Mon-Thur, the formula would just be Order Date + 1. However, if the Order Date is a Fri, it is Order Date +3; Sat, it is Order Date +2; Sun, it is Order Date +1.

=IF(WEEKDAY(L2,2)=6,L10+3)

So basically I want to incorporate three things into the formula:

If Order Date = 6 (Fri), Order Date +3
"         "        = 7 (Sat), Order Date +2
"         "        = 1 (Sun), Order Date +1
If Order Date = 2 (Mon),3(Tue),4(Wed),or 5(Thu), Order Date + 1

Any suggestions on how to accomplish this? I can do three different formulas but I was trying to see if I could do it all together.

Thanks

[Anne Troy]

So, wouldn't that be just:

=IF(WEEKDAY(E9)=6,E9+3,IF(WEEKDAY(E9)=7,E9+2,E9+1))

[Zack Barresse]

Possibly, also ...

=E9+CHOOSE(WEEKDAY(E9),1,1,1,1,1,3,2)

Note: Untested

[TonyJollans]

And what is wrong with ..

=WORKDAY(E9,1)

It might need the Analysis ToolPak AddIn, though.

[Zack Barresse]

.. It might need the Analysis ToolPak AddIn, though.

Yup, it does. I don't have this Add-in on my home pc, so I couldn't test. I knew it was something like that.

MRomanow: You know this will give you three dates that fall on a Monday (if the dates are consecutive), right? (Making sure I haven't missed anything.)

[patrickab]

Just tested it:

=WORKDAY(E9,1)

Works fine and returns the same date for Friday, Saturday and Sunday i.e. the following Monday