Find monday or saturday before date in excel

Question

I have a job list in excel 2010. Everyone has a designated date. The task must be submitted for external processing 3 working days before the due date. Before an assignment can be sent for external processing, it must be reviewed. Submissions for review are held on Mondays and Wednesdays.

I need a function that looks at a date in a date cell and returns the date of Monday or Wednesday (which is ever closer) to the date 3 business days before that date;

X = (3 business days before the due date)

send date = (Monday or Wednesday until X)

I got X that way; =WORKDAY.INTL(<due date cell>,-3)

Now I need a code to go to the sending date.

Answer 1

If your appointed date is Monday, and then 3 business days before it is on the previous Wednesday, then can you review on Wednesday, or is it necessary for Monday before that? If this is the last, then you can use WORKDAY and WORKDAY.INTL , as in the case of the estimated date in A2

=WORKDAY.INTL(WORKDAY(A2,-3),-1,"0101111")

If this is the first, then just do -3 at -2

With this approach, you use WORKDAY to return 2 or 3 business days, and then WORKDAY.INTL uses "0101111" to indicate the working days on Monday and Wednesday and subtracts another day based on that.

This method can be used for any number of days - the last number always remains -1

Answer 2

Ok, I tried to do this without the huge IF () operator, but it works. Your date is in A1 format, and your shipping date formula is lower in B1.

 =IF(WEEKDAY(A1)=1,A1-6,IF(WEEKDAY(A1)=2,A1-7,IF(WEEKDAY(A1)=3,A1-6,IF(WEEKDAY(A1)=4,A1-7,IF(WEEKDAY(A1)=5,A1-8,IF(WEEKDAY(A1)=6,A1-4,IF(WEEKDAY(A1)=7,A1-5))))))) 

Quite a lot of what I did, I found out how many days to subtract from each date when you deducted at least 3 business days, and then subtracted more days until you got to Wed. or mon When is your ...

• Maturity date; then (Days to deduct, to get your desired Mon or Wed Shipment date)
• Sunday; then -6
• Monday; then -7
• Tuesday; then -6
• Wednesday; then -7
• Thursday; then -8
• Friday; then -4
• Saturday; then -5

Hope this helps.

-Scheballs

EDIT

Thanks to Barry Houdini for showing me the CHOOSE () function.

 =A2-CHOOSE(WEEKDAY(A2),6,7,6,7,8,4,5) 

Quite a few times, when you find the offsets needed to subtract the days from your day of the week, you can use the WEEKDAY value as a position in the list of options. So if our date for the day of the week is Sunday, it is "1", and then our choice of the list of functions, the first one is selected. Our repayment date is then subtracted from this value. Very nice barry, thanks.