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!
Bookmarks