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

Question

I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday.
So since today is Friday September 4th it should populate the cell with 9/7/2020, this should remain the content of the cell until next Friday 9:30 pm.
When the function sees that next Friday September 11th 9:30 pm has arrived it should update the cell content to be 9/14/2020.

I came up with this formula:

=IF(OR(WEEKDAY(TODAY()) >= 6, WEEKDAY(TODAY()) <= 1), 
TO_DATE(value("2020-08-17 09:30") + 14*floor((now() - value("2020-08-17 09:30"))/7)),
TO_DATE(value("2020-08-03 09:30") + 7*floor((now() - value("2020-08-03 09:30"))/7)))

But it only works if the Date in quotes in the first TO_DATE() is the date for Monday 2 weeks prior to the week I want. So this worked for the Friday August 28th, it displayed 8/31/2020 but today since it is Friday it changed the date to 9/14/2020 which is actually 2 Mondays from now.

Disclaimer: I'm a complete noob when it comes to Google Sheets, I tried my best to come up with something by reading the Google docs and this is the best I was able to do.

Answer 1

Try this:

=if((WEEKDAY(today(),2))>=5,today()+8-(WEEKDAY(today(),2)),if((WEEKDAY(today(),2))<=4,today()-(WEEKDAY(today(),2))+1,""))

Explanation

• WEEKDAY(today(),2) - creates a number between 1 and 7; If the date is a Monday, then tit will return 1, Tuesday = 2, etc.
• if the day is greater than or equal to 5 (i.e. the day = Friday, Saturday, or Sunday), then add (8 days less the current day number) to the current date.
  • Result = next Monday.
• if the day is less than or equal to 4 (i.e. the day = Monday, Tuesday, Wednesday or Thursday), then subtract (the current day number plus 1) from the current date.
  • Result = lest Monday

Answer 2

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.