google sheets – Formula to output the date for next Friday once it has passed 20:00 on Friday

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 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 today’s date. 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 today’s date until after 8:00 PM.)