Randbetween until a specific day?

Question

I want to have the randbetween function only return a random number until a specific day, then stop changing. Is there a way to accomplish this in googlesheets?

=IF(today()<F1,randbetween(1,10),)

I tried something like this, but it would just go blank if it's false.

I agree with @MattKing, since the best way to do this will be with Google Apps script, since `randbetween(min,max)` will always change when the page is refreshed or the file is open. Will you be interested in using Apps Script? — Giselle Valladares, Aug 19 '22 at 18:31
@player0, that formula and the [post](https://stackoverflow.com/questions/66201364) was awesome! I learn something new thank you! — Giselle Valladares, Aug 22 '22 at 15:08

score 3 · Accepted Answer · answered Aug 19 '22 at 21:08

3

sure there is you just need to summon the animal overlord... and avoid using TODAY() directly in the formula

let F1 be your date
let F2 be =TODAY()

then use:

=IF(F2<F1, RANDBETWEEN(1, 10), 
 UNIQUE(REGEXEXTRACT(QUERY(SPLIT(FLATTEN(LEN(
 WHATTHEFOXSAY()&WHATTHEFOXSAY())&" "&TRANSPOSE(LEN(WHATTHEFOXSAY()&WHATTHEFOXSAY()))), " "), 
 "select Col1+Col2 label Col1+Col2''")&"", "\d$")+1))

demo sheet

to learn more about alchemy follow the great white fox

answered Aug 19 '22 at 21:08

player0

124,011
12
67
124

This seems to be working! Out of curiosity what is the false result rand between? – plerpsandplerps Aug 22 '22 at 11:32
@plerpsandplerps not sure I understand your question ? – player0 Aug 22 '22 at 12:01

Randbetween until a specific day?

1 Answers1

demo sheet

Linked