Formula to change status on anniversary

[Darren_Ledwich]

Hi there.
I want to create a base to track my receipts for warranty purposes.
I can just insert the purchase date in a column and track it myself, but what I would like to do is when the entry is one year old change its status in a dropdown column from “current” to “expired”.
Thanks

[Justin_Barrett]

Single-select fields (i.e. the dropdown you mentioned) cannot be modified by Airtable itself. You can change it manually, change it via custom code (i.e. the API or the Scripting block), or have an integration system like Zapier or Integromat make the change. My gut says the latter would be easiest.

• If you use Zapier, you would trigger the zap with the “New record in view” option, focusing on a custom view in Airtable that only shows records with the “Current” status and a purchase date older than one year. Any triggering records would have their status changed to “Expired.”
• For Integromat, you would need to build a scenario that runs on a regular schedule (once a day should be fine) searching for “Current” records with purchase dates older than one year from the current day (or you could use an Airtable formula field to do the date comparison, so that Integromat just needs to look for a specific value in that formula field). For any record found, change its status to “Expired.”

[ScottWorld]

Everything that @Justin_Barrett said is 100% true and will give you the result that you were specifically asking for.

However, why not just create a simple formula field in Airtable that automatically changes between “Current” and “Expired”? That would be the quickest & easiest way of solving this issue, without needing to jump through a lot of extra hoops.

[Darren_Ledwich]

Thanks @Justin_Barrett and @ScottWorld for the quick responses.
In hindsight it’s not essential to have the result as a dropdown. I had a crack at using Zapier and haven’t gotten that to work yet either. Can you help me with an example formula to insert?
Thanks Darren

[M_k]

Hi @Darren_Ledwich

I think that this link might be helpful to come up with a formula. Check, in particular, “Date and time fuctions”. Also, replace date or time or field1, field2 in the brackets with your specific field name/date from your database.

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

If this solution has been helpful, please add SOLVED to the subject line.

Thank you,
Mary Kay

[Darren_Ledwich · Accepted solution]

Thanks everyone for your assistance.
I found a similar formula in a template post on the Universe.
This is what I ended up using.
IF(DATETIME_DIFF(TODAY(), {Purchase Date}, ‘days’) < 365, ‘Current’, ‘Expired’)

[Justin_Barrett]

And that’s why I shouldn’t stay up so late perusing the forum. :slightly_smiling_face: That formula solution did cross my mind, but I got a bit too hyper focused on the single-select part of the original post.

[ScottWorld]

Lol!! :rofl: :joy: I know the feeling, though… this forum can be addicting! :joy: