Create new records for each week of another record's timeframe

Question

Thank you for considering to help me! Here's the process I'm trying to create:

A new record is created via a form which will have a start and end date (i.e. 7/3-7/28)
Based on the timeframe (above), I would like to set up an automation where additional new records are created (within the same table) for each week within that timeframe. (i.e. a new record with the dates 7/3-7/7; another record with the dates 7/10-7/14; another one for 7/17-7/21 and the last one for 7/24-7/28). There will be other fields that I'd like to copy from the original record as well.

I'd like to avoid a script and just use formulas and automations, but am open to a script (although will need help writing one!) if that's the best/only option.

Thank you!

Kamille_Parks11 · Accepted Answer

I took a crack at solving this without a script in the latest episode of the BuiltOnAir podcast: S15, E01. In short, you'll need one automation and two "helper" formula fields

https://www.youtube.com/live/G2zGaPN98zU?feature=share&t=2586

The formulas for the two helper fields discussed in my implementation:

⚙️Clamped End Date

The following formula will get the Friday of whatever week "Start Date" is in.

IF(
{Start Date},
DATEADD({Start Date}, 5-WEEKDAY({Start Date}), "days")
)

⚙️ Next Start Date

Te=he following formula will simply add one week to the "Start Date" if the "Clamped End Date" is before the "End Date". Essentially, if the time span is longer than a week, this field tells the automation that a new record needs to be created, and this date will be its "Start Date".

IF(
{⚙️ Clamped End Date} < {End Date},
DATEADD({Start Date}, 1, "week")
)

Wow, @Kamille_Parks11 !! I'm honored that you used my question on your podcast, THANK YOU! this solution is brilliant - exactly what I was looking for. I'll work on testing it and will let you know if I have nay questions. THANK YOU SO MUCH!

Lisa_Bauer · Answer