Google Spreadsheet: How to calculate the next Monday after a given date?

by Brian   Last Updated July 16, 2018 13:03 PM

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)
Tags : google-sheets


Answers 2


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))
Franck Dernoncourt
Franck Dernoncourt
October 23, 2013 22:51 PM

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))
Ashi
Ashi
July 16, 2018 12:59 PM

Related Questions



Copying values between google sheets

Updated July 26, 2018 21:03 PM