I have an Excel Worksheet that contains a date in Column A. This date "Begin Date" is always EQ to the first date of a given month (i.e. 04/01/14, 11/01/13, etc.) In Column B, I need to calculate the "Expiration Date" which is 13 months after the Begin Date and always the 6th Weekday of the month. For example, A1=04/01/14. Cell B1 should output 05/08/15 (the 6th Weekday of May). A second example would be, A1=10/01/14. Cell B1 should output 11/09/15 (the 6th Weekday of November). 

=EDATE(A1, 13) + IF(OR(WEEKDAY(EDATE(A1, 13)) = 7,  WEEKDAY(EDATE(A1, 13)) = 1), 9, 7)

This should do the trick.  If the date + 13 months is a Saturday or Sunday, it will always be the 10th of the month else it is the 8th.  This is based on the default WEEKDAY setting of Sunday = 1 and Saturday = 7

Regards

I may be missing something, but when I use the formula provided, it doesn't calc properly. For example, in A1 "Begin Date" the value is 10/01/14, then B1 should calculate 11/09/15 which is the 6th Weekday. The formula is returning 11/10/15 which is the 7th Weekday.

Any suggestions?

=EDATE(A1, 13) + IF(WEEKDAY(EDATE(A1, 13)) = 7, 9, IF( WEEKDAY(EDATE(A1, 13)) = 1, 8, 7))

Apologies, the above is the correct formula.  I calculated Sunday and Saturday to have the same days until the 6th working day, Saturday has one more day than Sunday.

• Edited by Laurence Cross 10 hours 32 minutes ago
• Marked as answer by David in a WC 10 hours 12 minutes ago

It works!!!

You have made my day. Thank you very much for the assistance.

=EDATE(A1, 13) + IF(WEEKDAY(EDATE(A1, 13)) = 7, 9, IF( WEEKDAY(EDATE(A1, 13)) = 1, 8, 7))

Apologies, the above is the correct formula.  I calculated Sunday and Saturday to have the same days until the 6th working day, Saturday has one more day than Sunday.

• Edited by Laurence Cross Thursday, June 04, 2015 8:58 PM
• Marked as answer by David in a WC Thursday, June 04, 2015 9:18 PM