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

[Lisa_Bauer]

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

1. A new record is created via a form which will have a start and end date (i.e. 7/3-7/28)
2. 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_Parks · Accepted solution]

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")

)

---

[Lisa_Bauer]

Wow, @Kamille_Parks !! 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!