Google-sheets – How to change the date to the coming Monday every Friday at 9:30 pm in Google Sheets

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-dates

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.

Best Answer

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
Related Topic