How to generate a random date?

1

I just want to generate a bunch of random dates, how do I do that?

2

like this?

image
image1274Ă—525 27.6 KB

image
image997Ă—479 17.9 KB

cheers, Christiaan

2 Likes
3

Are you looking to add them to a table? How many dates total? And between what range?

1 Like
4

Let’s say I want to add a function to “Value for new rows” that will generate a random date in a range whenever a new row is created.

5

As suggested by @Christiaan_Huizer :

RandomItem(
  Sequence(
    Date(2000,01,01),Today()
    ),false
)

Something like this :point_up_2: seems to work as Value for new row in a Date column for rows added manually (i.e.: not with a button/automation) :blush:

3 Likes
6

the suggestion of @Pch gave rise to this idea, short and simple as value for new row.

sequence(EpochToDate(now()),Today()).RandomItem()

Cheers, Christiaan

2 Likes
7

Depending on how Sequence() is implemented, this could potentially be a costly operation.

I would recommend working with unix timestamps (“epochs” as they are referred to in the Coda world) and then converting to the corresponding date.

For example, any random date between 1/1/1970 and today:

EpochToDate(RandomInteger(0, Today().ToSeconds() - Date(1970, 1, 1))).ToDate()

In general, to get a random date between startDate (that is on or after 1/1/1970) and endDate, you can use:

EpochToDate(RandomInteger(startDate.ToSeconds() - Date(1970, 1, 1), endDate.ToSeconds() - Date(1970, 1, 1))).ToDate()

If either startDate or endDate are datetimes, you can clear the time value with ToDate() to make sure that the distribution is completely based on calendar days and not time of days (which would only really be noticeable if the date range is small).

Of course, provided that Sequence()'s implementation is good, then this isn’t necessary. I would assume that it creates some sort of generator under the hood and provides lazy indexing, which would yield the same performance (O(1)) as the above approach.

1 Like
8

Thinking about my earlier suggestion …

Using RandomInteger() instead of RandomItem() would probably be more helpful and efficient here as there would be no need to use Sequence() to create an invisible list of dates each time just to pick one :blush: .

And this formula should be sufficient to create a random date as Values for new row (in a Date field) :blush:

RandomInteger(Date(2020,01,01),Today())
9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.