Nested Function with 'EDTATE' and "WEEKDAY'

Question

Hello Everyone,

, I want to subtract 2 month from a date (deadline for request vacation).  The edate function will do this beautifully and is what I want.  Now here is the kicker if the ‘return date falls in a Saturday or a Sunday then the deadline will be the following Monday

 I have used a formula (weekday nested with edates), that I copy from a previous question but I couldn’t modify for Monday; “=EDATE(B4,-2)-2-IF(WEEKDAY(EDATE(B4,-2),2)=7,0,WEEKDAY(EDATE(B4,-2),2))” (my data starts in B4)

Can anyone help me to develop a following Monday deadline???? Here is some of the data:

Date                Deadline Date                                     Actual Formula should be ?

Using Edate formula                           Should show:

1/1/2014          November 01, 2013    Friday                11/01/13

1/20/2014        November 20, 2013    Wednesday        11/20/13

2/17/2014        December 17, 2013     Tuesday             12/17/13

7/4/2014          May 04, 2014             Sunday         5/5/14

9/1/2014          July 01, 2014               Tuesday             7/01/14

11/4/2014        September 04, 2014    Thursday            9/4/13

11/27/2014      September 27, 2014   Saturday     9/29/14

11/28/2014      September 28, 2014   Sunday        9/29/14

12/24/2014      October 24, 2014        Friday                 10/24/14

12/25/2014      October 25, 2014       Saturday       10/27/14

1/1/2015          November 01, 2014   Saturday       11/03/14

Answer 1

THANK YOU SO MUCH! Mike,  It work!!!.  Can you explain to me the use " ,2) 0,0,0,02,1)' is this counts the day?

Hi,

Glad that worked and thanks for the feed back. How it works is very simple

=EDATE(A2,-2)+CHOOSE(WEEKDAY(EDATE(A2,-2),2),0,0,0,0,0,2,1)

We get a date 2 months in the past with the first underlined bit and in the seconds underlined bit we get the date again  and using the WEEKDAY function we check which day of the week it is. Monday returns 1 thru Sunday which returns 7.

Now we use the CHOOSE bit which takes that number 1 thru 7 and it adds the number of days specified in the Bold bit so for Monday thru Friday ( the first 5 zeroes) it adds zero but for Saturday (6) it adds the 6th number in bold which is 2 and we get Monday and for Sunday (7) it adds the 7th number in the list (1) and likewise we get Monday.

Answer 2

Hi,

With your first date in A2, Put this in a cell and drag down as required.

=EDATE(A2,-2)+CHOOSE(WEEKDAY(EDATE(A2,-2),2),0,0,0,0,0,2,1)

Answer 3

Or,

You could use WORKDAY function which will avoid Sat & Sun. Also holidays if you have.

=WORKDAY(EDATE(B4,-2)-1,1)

Answer 4

Thank you Mike for the explanation.  i always try to learn every time i get to use a new formula.  Have a wonderful day!!

Answer 5

THANK YOU SO MUCH! Mike,  It work!!!.  Can you explain to me the use " ,2) 0,0,0,02,1)' is this counts the day?