# Date recurring on a yearly basis every year?

Topic Labels: Dates & Timezones Formulas
Solved
1171 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi fellow Airtablers!

Could anyone suggest how can I make the ''Next charge'' date field recurring on a yearly basis every year if the toggle in ''Frequency'' is set to ''Yearly''?
This dilemma is driving me nuts 😞

Right now, I just have a formula in that field:

But it's not ideal because I would need to manually update ''Latest Charge'' field with the previous year every year.
Ideally, I'd love to solve this with a formula because I'd have lots of entries, and it may be a bit cumbersome to create automation for each and every entry.

But if automation to update the latest charge is absolutely needed, what is the best way to go about it/set it up?

1 Solution

Accepted Solutions
8 - Airtable Astronomer

Hey @1ikigai , if you're still having issues here's an alternate approach. I recorded a Loom video to explain how this automation works (watch it first).

Here's the table overview:

I use 3 dates here to keep track of time: Initial Charge, Last Charge, and Next Charge.

Initial Charge is when the first date a charge occurred.

For last Last Charge, I set it to a copy/past of Initial Charge to start, and then the automation takes over to update it. And

And Next Charge is the frequency (weekly, monthly, yearly) added to Last Charge.

### Remember to update all Table / Field / Variable names to match YOURS ###

Formula for Next Charge:

``````IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Weekly'
),
IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Monthly'
),
IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Yearly'
),
)
)
)``````

Trigger Reminder indicates when the automation is ready to run again, by displaying "Run Trigger"

Formula for Trigger Reminder:

``````IF(
AND(
TODAY() >= {Next Charge},
{Last Charge}
),

"Run Trigger"
)``````

The Automation is two parts. The first is the Trigger which is set to 'When record matches condition', and the condition is 'when Trigger Reminder contains Run Trigger'

And the 2nd is the script. The Script requires 3 input variables: recordID, lastCharge and renewalFrequency

And here's the script (remember to update variable names to match yours)

``````// Select Table
var subscriptionTable = base.getTable("Subscription Renewal");

// Select Input Values
var inputConfig = input.config()
var lastCharge = inputConfig.lastCharge;
var lastChargeDate = new Date(lastCharge)
var recordID = inputConfig.recordID;
var renewalFrequency = inputConfig.renewalFrequency;
var newDate;

// Selecting Weekly, Monthly or Yearly Renewal
if (renewalFrequency == 'Weekly') {
newDate = new Date(lastChargeDate.setDate(lastChargeDate.getDate()+7));
} else if (renewalFrequency == 'Monthly') {
newDate = new Date(lastChargeDate.setMonth(lastChargeDate.getMonth()+1));
} else if (renewalFrequency == 'Yearly') {
newDate = new Date(lastChargeDate.setFullYear(lastChargeDate.getFullYear()+1));
}

// Deleting Existing Last Charge Data (To Allow Automation To Retrigger)
"id": recordID,
fields: {
"Last Charge": ""
}
}]

// Updating Last Charge Date With New Date
"id": recordID,
fields: {
"Last Charge": newDate
}
}]

Let me know if that worked for you!!

5 Replies 5
6 - Interface Innovator

Hi,

I've coincidentally just answered this in another post, so here's a link to that:

https://community.airtable.com/t5/automations/recurring-reminder-auto-update-date-fields/td-p/168564

You've done most of the work already, so it really is just a case of setting up an automation to move the Next Charge to the Latest Charge field when your trigger is triggered, whatever that might be (eg When record matches conditions -> When Latest Charge - is - today)

This will run for every record in that table automatically, when triggered (in my example, simply when the Latest Charge date is today), so the number of entries shouldn't matter, unless I've misunderstood.

5 - Automation Enthusiast

ohhhh this might be it!

I loved your IF statements solution for doing annual/monthly thingy, but for some reason getting an error for that formula.
Do you think something must be changed in it or maybe I'm doing something wrong?

My formula looks like this:

IF (Frequency} = "Yearly",
IF(
{frequency} = "Monthly",
""
)
)

But I'm getting this error:

8 - Airtable Astronomer

Hey @1ikigai , if you're still having issues here's an alternate approach. I recorded a Loom video to explain how this automation works (watch it first).

Here's the table overview:

I use 3 dates here to keep track of time: Initial Charge, Last Charge, and Next Charge.

Initial Charge is when the first date a charge occurred.

For last Last Charge, I set it to a copy/past of Initial Charge to start, and then the automation takes over to update it. And

And Next Charge is the frequency (weekly, monthly, yearly) added to Last Charge.

### Remember to update all Table / Field / Variable names to match YOURS ###

Formula for Next Charge:

``````IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Weekly'
),
IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Monthly'
),
IF(
AND(
{Last Charge},
{Renewal Frequency} = 'Yearly'
),
)
)
)``````

Trigger Reminder indicates when the automation is ready to run again, by displaying "Run Trigger"

Formula for Trigger Reminder:

``````IF(
AND(
TODAY() >= {Next Charge},
{Last Charge}
),

"Run Trigger"
)``````

The Automation is two parts. The first is the Trigger which is set to 'When record matches condition', and the condition is 'when Trigger Reminder contains Run Trigger'

And the 2nd is the script. The Script requires 3 input variables: recordID, lastCharge and renewalFrequency

And here's the script (remember to update variable names to match yours)

``````// Select Table
var subscriptionTable = base.getTable("Subscription Renewal");

// Select Input Values
var inputConfig = input.config()
var lastCharge = inputConfig.lastCharge;
var lastChargeDate = new Date(lastCharge)
var recordID = inputConfig.recordID;
var renewalFrequency = inputConfig.renewalFrequency;
var newDate;

// Selecting Weekly, Monthly or Yearly Renewal
if (renewalFrequency == 'Weekly') {
newDate = new Date(lastChargeDate.setDate(lastChargeDate.getDate()+7));
} else if (renewalFrequency == 'Monthly') {
newDate = new Date(lastChargeDate.setMonth(lastChargeDate.getMonth()+1));
} else if (renewalFrequency == 'Yearly') {
newDate = new Date(lastChargeDate.setFullYear(lastChargeDate.getFullYear()+1));
}

// Deleting Existing Last Charge Data (To Allow Automation To Retrigger)
"id": recordID,
fields: {
"Last Charge": ""
}
}]

// Updating Last Charge Date With New Date
"id": recordID,
fields: {
"Last Charge": newDate
}
}]

Let me know if that worked for you!!

6 - Interface Innovator

You're just missing a bracket around the first Frequency field:

IF({Frequency} = "Yearly",
IF(
{frequency} = "Monthly",
""
)
)

And actually I think I made a mistake, in that you don't need the "+1" in the dateadd formula., just "1", so I've removed that.

Give that a try!
5 - Automation Enthusiast

Guys, thank you so, so much!