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
Solved
Formula to change status on anniversary
Best answer by Darren_Ledwich
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’)
+21Single-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.”
+35Everything 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.
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.
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
+20Thanks @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
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.
If this solution has been helpful, please add SOLVED to the subject line.
Thank you,
Mary Kay
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’)
+21Everything 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.
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.
+35And 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.
Lol!! :rofl: :joy: I know the feeling, though… this forum can be addicting! :joy:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.