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
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.)
-
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 '20 at 0:07
-
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 weekMOD(5-WEEKDAY(today(),2),7): the number of days until the next FridayMOD(NOW(),1): the current timetime(20,0,0): time of 20:00:00today(): current date
