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. :D

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