-1

I would like a formula to output the date for the upcoming Friday and switch to the next Friday once it passes 20:00 on Friday night.

I have tried some solutions from other questions however I can never seem to get the correct output

2

Alsors, try this:

=ArrayFormula(IF((WEEKDAY(TODAY())=6)*(NOW()-TODAY()>=TIMEVALUE("8:00 PM")),TODAY()+7,FILTER(SEQUENCE(7,1,TODAY()),WEEKDAY(SEQUENCE(7,1,TODAY()))=6)))

HOW IT WORKS

The formula assumes Sunday is counted as weekday 1 and, therefore, that Friday is counted as 6.

The first part of the IF checks to see if two conditions are true: that "today" is Friday and that the current time is greater than or equal to 8:00 PM. If those conditions are both TRUE, then the date is set to seven days in the future (i.e., next Friday).

If this small window of time is not the case, then the second half of the IF statement takes effect. A SEQUENCE is formed of seven dates starting with the date for "today". FILTER filters in only those days whose weekday is 6 (i.e., Friday). And since there will only be one Friday within any seven-day SEQUENCE of dates, it will pull whatever the "next Friday" is.

(Note: If "today" is Friday before 8:00 PM, then the "next Friday" in the SEQUENCE is "today," so it stays showing the current day's date until after 8:00 PM.)

| improve this answer | |
  • Thanks a lot dude! Not only have you given me the formula you've explained how it works to better my understanding. This is much appreciated! – Alsors Nov 22 at 0:07
  • Happy to have helped you out, Alsors. – Erik Tyler Nov 22 at 0:15
1

It is not surprising that there might be several ways to solve this question. This is offered as an alternative.


=if(weekday(today())<>5,today()+MOD(5-WEEKDAY(today(),2),7),IF(MOD(NOW(),1)>time(20,0,0),today(),today()+MOD(5-WEEKDAY(TODAY(,2),7))))


Logic

If the current date is not a Friday, then display the date of the next Friday.

If the current date IS a FRiday, then compare the current time to 20:00:00, if the time is earlier, then display the current date, otherwise display the date of the next Friday.

  • weekday() is used to identify the day of the week
  • MOD(5-WEEKDAY(today(),2),7): the number of days until the next Friday
  • MOD(NOW(),1): the current time
  • time(20,0,0): time of 20:00:00
  • today(): current date
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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