9

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)
14

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))
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)
0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.