Increment Date Field by one month when another field changes value

[Henry_Valevich]

I have 2 fields: PrevDate and NextDate. When I change another field or add checkbox I need to increment NextDate by 1 month from PrevDate. I can use DATEADD({PrevDate}, 1, ‘month’) but then I need to change PrevDate so that I can use use it nest month,

[Rupert_Hoffsch1]

Hi Henry, can you specify what you’re trying to do? Why does the formula not work? If the change shall be contingent on a field change or checkbox, you need to use an automation (maybe a small script would be the easiest).

[Henry_Valevich]

Hi Rupert, the formula works on next-day field since it’s using prevday field but then I need to change prevdate and I can’t use the formula since I get an error message circular…
Basically I need to bump up same field by 1 month. Every month due date need to change by one month. Let’s say I have a bill that is due on 4th of each month. My next due date is 6/4 so when I add a payment in payment field I want my due date to increase by one month.

I hope clear. Thanks.

[Rupert_Hoffsch1]

Unfortunately not, I don’t understand what you’re trying to do. If you have a date field which is changing, why does the formula not work? Or do you want to automatically calculate due dates?

[Henry_Valevich]

Rupert,

I’m sorry I can’t explain myself properly. Let me show you my example table.

When I enter amount I have a formula in Paid Date as today’s date but now I need to bump up NextDate field by a month. I have to use the current value in NextDate and from today.

[Rupert_Hoffsch1]

So when you change the amount, you want to add one month to NextDate from today?

In this case, I would create another formula field, which constantly calculates today + 1 month:

DATEADD(TODAY(), 1, 'month')

Then create an automation which uses changes in the Paid Amount field as a trigger. The action step in that automation is that you’re writing whatever the today + 1 month formula is in a separate date field to preserve that value.

Hope that makes sense!

[Henry_Valevich]

Hi. No, not from today but from NextDate. That’s the issue, it’s easy from today but NextDate already has a value that is not necessary today’s .

Thanks for trying.

[kuovonne]

Welcome to the Airtable community!

It sounds like you have multiple records that are chained together with same-table links. You want both the {PrevDate} and {NextDate} and possible a {Due Date} to be calculated fields, which is easy to do in a spreadsheet. However when you try to make the last date calculated, you get the circular reference error from Airtable. This is one way how Airtable is different from a spreadsheet.

In Airtable you must create a gap in the circular logic and then bridge that gap. One way to do this is to use an automation to move a value from a calculated field into an editable field.

The specifics of how you trigger the automation depends on how you create the new record when you pay the bill and how you create the link. I am assuming that you create the new record manually in the grid view on the date when you pay the bill, and you manually link to the new record to the previous record.

Have an editable {Due Date} field and a lookup of the {NextDate} field of the linked previous record. Have an automation move the lookup of the {NextDate} to the {Due Date} field.