Once due date is met, start new row based on recurrence interval

Tammy Luther

I have some reports that are due at different intervals, 1 year, 5 years, etc. I want to create a formula (or method) that when the due date is reached it will set the next due date. It would actually be neat if the system would generate the next line, but I can live with just doing the formula.

And need to due this if the recurring column is yes.

if recurring is no - then do nothing.

I have tried:

=DATE(YEAR([Due Date]@row) + ([Recurring Intervals Years]@row), MONTH([Due Date]@row), DAY([Due Date]@row)))

but I keep getting #UNPARSEABLE. Not sure where my formula is in error.

TY IA

MCorbin

I would check the formatting of the Recurring Intervals Years column - it appears to be thinking it's a text field. Try changing the format to make it a number (right justify)? But if that doesn't work, you could try this:

=DATE(YEAR([Due Date]@row) + VALUE([Recurring Intervals Years]@row), MONTH([Due Date]@row, DAY([Due Date]@row)))

For recurring rows, I usually create a "Master" sheet, with automation that copies my recurring row to my "Working" sheet as appropriate.

Master sheet "Due Date" would have a formula like =date(year(today()),11,2) for that row. You could create a different formula for every row based on the due date, OR see my "Option 2" below (which is better)

Then the automation in the master sheet copies that row to your "Working" based on the Due Date - maybe 90 days before? (Depending on how much lead time you need for creating your reports).

It gets a little trickier for due dates in the first 30 days of the year (based on copying 90 days before the due date) - in that case, you'd have a formula that looks like =date(year(today())+1,1,2)

Or if you really wanted to get tricky: (Option 2)

Keep your original due date (no need for it to change). Create a Current Due date column:

=IF(MONTH([Original Due Date]@row) <= 3, DATE(YEAR(TODAY()) + 1, MONTH([Original Due Date]@row), DAY([Original Due Date]@row)), DATE(YEAR(TODAY()), MONTH([Original Due Date]@row), DAY([Original Due Date]@row)))

With this:

• Create the automation that copies the master row to the Working sheet x days before the "Current Due Date"
• Since your master sheet is refreshing every day, on 1/1/23, all the dates after 4/1 will update to 2023 (see the "NOTE" below)
• If you don't need 90 days, then you'd edit the first part of the formula - IF(MONTH([Original Due Date]@row) <= 3, - to reflect the amount of lead time you need.

NOTE: Whenever you create automation based on a date that is using the "Today()" function in a formula, it is best to also add automation that refreshes that sheet daily, so the date formula gets refreshed as well.

The easiest way to do this is to create 2 automations:

Lock Row: Runs daily, 12:00 AM, for any row where your key field is not blank, Action = "Lock Row"

Unlock Row: Runs daily, 1:00 AM, for any row where your key field is not blank, Action = "Unlock Row"

Tammy Luther

TY - so i tried the formula and still got an error

I also want to build in the if recurring is YES (some may be no).

Lastly, I checked the format of the Recurring Intervals and had it set to drop down, changed to text/number option.