0

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.

2 Answers 2

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
1

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.

1
  • Thanks! I will just have to monitor the activity of the cell this coming week to see if the TODAY() and NOW() work as intended. Sep 5, 2020 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.