I am looking for a formula that can look at a cell value that contains a date and return the first Monday to occur after that date.
Examples (MM/dd/yyyy format):
10/22/2013 (Tuesday) would return 10/28/2013 (Monday)
10/28/2013 (Monday) would return 10/28/2013 (Monday)
Let B2 the cell containing the date.
=IF(WEEKDAY(B2)=2, B2, IF(WEEKDAY(B2)=1,B2+1, B2+9-WEEKDAY(B2)))
or
=IF(WEEKDAY(B2,3)=0, B2, B2+7-WEEKDAY(B2,3))
A bit more elegant solution is to shift each input by the proper number of days.
=B2+MOD(9-WEEKDAY(B2),7)
If you want to be clever, WEEKDAY isn't needed as we know that the epoch fell on a Saturday (calculate WEEKDAY(0) to check this)
=B2+MOD(9-B2,7)
A generic and elegant solution that works for any given day of the week (N-day):
Cell B2: date to change to the next N-day of the week;
Cell B3: next N-day of the week: Mon=1,Tue=2,...;
=B2+MOD(B3-WEEKDAY(B2,2),7)
For a specific day of the week, in this case Monday (day=1), the formula can be 'hard-coded' to
=B2+MOD(1-WEEKDAY(B2,2),7)
Note that by setting the 2nd parameter of weekday to '2' weekday(B2,2) this returns the day of the week consistent with the desired N-day used in the above formula (cell B1), i.e. Mon=1,Tue=2,...
Franck's solution is perfect for Monday but it won't work for other days (I only tested 2nd formula). However, the following formula will work for other days. Let B2 is the date and B3 is the day (B3 is from 1 to 7 where Sunday is 1):
=IF(WEEKDAY(B2,1)<B3, B2+B3-WEEKDAY(B2,1), B2+B3+7-WEEKDAY(B2,1))
Note: This will find the next day even if B2 is the requested day. But the next formula will return today if B2 is the requested day:
=IF(WEEKDAY(B2,1)<=B3, B2+B3-WEEKDAY(B2,1), B2+B3+7-WEEKDAY(B2,1))
