How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?

See if this works for you:



The second argument of the WEEKDAY functions, which I have set to 2 each time in the formula, tells the formula which day of the week to consider “day 1.” In this case, 2 will set the first day of the week to Monday.

From there, we have a series of AND (+) and OR (*) logical tests. If the WEEKDAY of TODAY() is less than four (i.e., today is M, T, W or TH) -OR- if the WEEKDAY is five AND it’s before 9:30 PM, then we subtract one less than today’s WEEKDAY to get us back to the previous Monday.

If none of the above is the case (i.e., it is either Friday after 9:30 PM, or it is Saturday or Sunday), then we add the difference between 8 and the WEEKDAY (i.e., add 3 days if it’s Friday, 2 if it’s Saturday and 1 if it’s Sunday) to get us to the following Monday.

This is a tough one to test, since TODAY() and NOW() are not manipulable; but logically, that formula should work.