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:

=IF((WEEKDAY(TODAY(),2)<=4)+((WEEKDAY(TODAY(),2)=5)*(NOW()-TODAY()<TIMEVALUE("9:30 PM")))=1,TODAY()-(WEEKDAY(TODAY(),2)-1),TODAY()+(8-WEEKDAY(TODAY(),2)))

NOTES:

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.