Recurring Tasks (Subscriptions). Suggestions?

[Vitaliy_Malanchuk]

Hi Coda Friends,

I am creating a subscription tracker. I have due date for each subscription (specific date) and they occur on different cycles - monthly, annual, and sometime every few months and etc. But let’s focus on monthly and annual cycles.

I would like to have a due date change automatically on due date 11:59 PM for example. So for example if my Coda subscription is due Aug 5, 2023 on that date, the last minute of the day, I want Coda to go and change a year to Aug 5, 2024 (annual occurrence). If I have some formula and add 365 days, sometime depending on a year the date will get messed up and it will no longer be Aug 5th. Same goes for monthly subscriptions, for example for G-Suite, I have subscription where I am charged every 7th of the month and on that date need to add 1 more month but so its on the same 7th of each month.

I did check Automations - Time Based - but there is no such options (in the app I am moving from to Coda, we can have date adjusted +1 month, + 1 year + 3 months on a specific time of the day, so that was an easy solution).

What are some solutions to have recurring dates work where dates are always the same and automatically adjusted.

Thank you in advance for your help and suggestions!

[Piet_Strydom]

Hi Vitaly,

You can try something like the below:

It is the code I have in a button in a table in a document I have. You can push this button using the automation that you already have in place. The answer that you are looking for is the relativeDate() formula, which will add a month. (Not 30 days, or 31 days.)

Regards
Piet

[Vitaliy_Malanchuk]

Hi Piet, thank you for your response. Just to clarify, if I have columns - Due Date and Cycle (Annual or Monthly), I would Have the following formula in a button?

SwitchIf (Cycle=Monthly, 
ModifyRows(thisRow, DueDate,RelativeDate(thisRow.DueDate,1),
Cycle=Annual,
ModifyRows(thisRow,DueDate,RelativeDate(thisRow.DueDate,12)

And then I would need a automation that would automatically click the button? What would be the automation to make sure it ads (month or year) only on/after due date and not keeps on adding more than needed.

Thanks a lot!

[Kuovonne]

@Vitaliy_Malanchuk

And then I would need a automation that would automatically click the button? What would be the automation to make sure it ads (month or year) only on/after due date and not keeps on adding more than needed.

I believe you need a scheduled automation that runs every day or month (depending on the number of automations runs you have on your subscription plan and how close to the exact date you want to push the button).

Then have your automation filter the rows in the table based on comparing the current date with Now() or Today(). Only push the button for the rows that pass the filter.

I’m on a phone and have trouble typing formulas on my phone, but the general idea would be

[table]
  .Filter(CurrentValue.dateField = Today())
  .ForEach( 
    SwitchIf (CurrentValue.Cycle=Monthly, 
CurrentValue.ModifyRows(DueDate,RelativeDate(CurrentValue.DueDate,1)),
Cycle=Annual,
CurrentValue.ModifyRows(DueDate,RelativeDate(CurrentValue.DueDate,12))
  )

If you only run the automation once a month, you will need a different method to compare dates, such as
CurrentValue.Date.Month() = Today.Month()

[Vitaliy_Malanchuk]

Hi @Kuovonne and @Piet_Strydom . I took your suggestions and figured out the way it works for me.
I have Due Date, # column with formula that calculates days until Due Date, and a button switchif and relative date formula , and button disabled if Days Until Renewal is 0 or more. Then I have automation run once daily to click the button.

Thanks so much for your suggestions that gave me a light to solve the issue in a way I need it to work.