Finding next Weekday based on Dropdown list

Hey Everyone,

I'm trying to come up with a formula that will take a date from a certain cell, look at a drop down list (Monday to Sunday) and then return the next available selected Weekday.

IE: If I typed in June 2nd 2017 (Friday), selected Monday on the drop down, the value should return as June 5th 2017 (Monday).

This is what I am currently using, it kinda works, but I noticed that it is skipping a week sometimes.

=IF(AND($J$26="Abroad",$J$27="Wednesday"),$F$25+7-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Tuesday"),$F$25+6-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Monday"),$F$25+5-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Thursday"),$F$25+8-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Friday"),$F$25+9-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Saturday"),$F$25+10-MOD(4+WEEKDAY($F$25,2),7),
IF(AND($J$26="Abroad",$J$27="Sunday"),$F$25+11-MOD(4+WEEKDAY($F$25,2),7),$F$25)))))))

I noticed that when I put in June 1st (Thursday) and select Friday in the dropdown, it will return June 9th (Friday) instead of June 2nd (Friday) skipping a week.

Any help would be greatly appreciated!

Hi wvtn- This formula calculates the next date of any specified weekday.
E1 = Date entered
F1 = Specified Weekday (1-7) = (MON-SUN)
=E1+MOD(7-WEEKDAY(E1,2)+F1,7)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Formula:

Please Login or Register  to view this content.

Date in A1 Weekday in B1

Originally Posted by leelnich

F1 = Specified Weekday (1-7) = (MON-SUN)

... and to convert Weekday text (in J27) to a number:

=MATCH(LEFT($J$27,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee