Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Random dates inside a month

Topic Labels: Dates & Timezones
1492 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Hector_J_Carr
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! Im having trouble wrapping my head aroubd this but I am trying to duplicate records and assign then a random date inside the current month and then make them not to duplicate on the same date.

So duplicate 1 = month/day 5
Duplicate 2 = month/day 7

And so on on a random fashion but checks not to create one on the same date as another duplicate. I will have them linked by month.

Any help appreciated

1 Reply 1
Joachim_Brindea
6 - Interface Innovator
6 - Interface Innovator

Hello,

Don't know if you solved it yet, but here is what I use to publish an article within the next 30 days at random:

 

 

 

DATEADD(CREATED_TIME(),IF(VALUE(RECORD_ID()) <= 0, 1, IF(VALUE(RECORD_ID()) >= 1000000, 30, MOD(VALUE(RECORD_ID()), 30) + 1)),"day")

 

 

You should easily be able to adapt that, to duplicate within the same month. Don't try to identify duplicates, just make it impossible to have duplicates by using algebra to generate the date!

Something like this if your first record has a date column:

 

 

IF(
  DATETIME_FORMAT({Date}, 'D') > 15,
  DATETIME_FORMAT({Date}, 'YYYY-M') & "-" & IF(
    VALUE(RECORD_ID()) <= 0,
    1,
    IF(
      VALUE(RECORD_ID()) >= 1000000,
      30 - DATETIME_FORMAT({Date}, 'D'),
      MOD(VALUE(RECORD_ID()), 30 - DATETIME_FORMAT({Date}, 'D')) + 1
    )
  ),
  DATETIME_FORMAT({Date}, 'YYYY-M') & "-" & IF(
    VALUE(RECORD_ID()) <= 0,
    1,
    IF(
      VALUE(RECORD_ID()) >= 1000000,
      30 - DATETIME_FORMAT({Date}, 'D'),
      MOD(VALUE(RECORD_ID()), 30 - DATETIME_FORMAT({Date}, 'D')) + 1
    )
  )
)

 

Cheers 👋