DATETIME format w/ quarterly dates

[Kyle_Herrod]

Hi Airtable Community!
I’m looking for some help with creating an automation or script (I don’t know if it’s that complicated).
I need the record called Date of RTL to be updated WHEN the record called "RTL = YES.
The date that needs to populate is the date in which the condition becomes true in RTL.
Example: A5 Liquor was RTL=YES on 8/17/2020, which I manually selected.

Right now it’s a manual process and as you can see, I’ve forgotten to update it a few times. It does not have to retroactively update the Date of RTL, but would be helpful.

Cheers!

[Marko_Petrovic]

If i understood you correctly, this is how you could set it up (I am not an expert so there might be fancier way but it will work:
First you will need a formula field with Today() function that will store current date.
Then setup automation that will run “when record matches a condition” where condition is when RTL is YES
Action will be Update record, where you will update Date of RTL field with data from the field with Today() formula.

I am sorry for a bit of fast instructions, when I get hold of a PC i could give you more details if you don’t succeed.

[Kyle_Herrod]

Marko, thank you for your reply! If you’re able to provide some formulated steps, I’d appreciate it! I’m still learning the functions and formulas.

[Kamille_Parks]

You can skip Automations and just make the {Date of RTL} a Formula field instead. Using the following formula, you can have the date at which the field value became “Yes” entered automatically:

IF({RTL} = "YES", LAST_MODIFIED_TIME({RTL}))

This will give you the dates for all the "YES"s you forgot to enter.

Caveats: you won’t be able to edit the date at all, and if the field becomes anything other than “YES” the date will disappear. If those are issues, use an Automation instead as Marko suggested.

[Carter_W]

Hello @Kyle_Herrod ,
I’d Glad to assist you.
Pm Sent with details, please check.
Thanks and Regards
Carter W

[Kyle_Herrod]

Kamille,

This is perfect! Thank you so much.

Cheers,

[Marko_Petrovic]

Here is how to setup automation to insert today’s date when you change status to Yes.

First you need to setup field with formula for today’s date (you can hide this field in view later if you don’t want it to add clutter):

And here is how automation setup should look (Its based on fields from my dummy table so names might be different)
Trigger part:

Action part:

Hope this will help :slightly_smiling_face: