Updating Status After Time Period

[oktopus]

Hi everyone,

I need some help to build some automations please.

I would like to modify status after specific period.

For example, when a new record is create, the field "creation date" fill be filled.

for records below 3 days I want to automatically change the status to "Less than 3 days"

for records between 3 days and 5 days I want the status "3 to 5 days"

records between 5 days and 10 days, status "5 to 10 days"

and below 10 days will have no status.

I already tried to make automations with "on this date or later" and "on this date and earlier" but it's not working properly.

Thanks for your help and merry christmas!

[Alexey_Gusev]

HI, 
use formula DATETIME_DIFF(TODAY(), {Created date} , 'days')
then 
IF(Result<3, 'Less than 3 days,
  IF(Result<5,'3 to 5 days',
    IF(Result<=10,'5 to 10 days', ' ' )
  )
)
You can use additional Result column, or replace 'Result' with first formula above and get the final output without additional columns.

[oktopus]

Hi Alexey,

Thanks for your help. It's not possible to use automation to make it? Because I would like the formula to change the status (because my status have different colors so record in Kanban will appear with the selected colors)

[Alexey_Gusev]

Hi,
I got it. Sure, there are many different ways to do it via automation. I just usually try to do it by formula only, if it's possible. But I can agree that some Airtable UI features are worth adding extra complexity.

The simple way is to add my formulas and create automation "When record updated" - 'Formula field' and action might be update Select field with value from formula.

Another way - add three automations (or four, last to clear select field after 10 days). In fact, you can add first 'Less than 3 days' as a default for new record. So, there are three automations needed: 'for 3 to 5' , 'for 5 to 10' and for 'after 10'.  Make it "when record matches condition", like that (example is for 3 to 5 days):

I hope you got it.

Note that if you want it for Kanban colors only, you can use record coloring, in the similar way, but with 1 condition per color, because conditions are checked one by one, and if first 'Less than 3' is true, others ignored. Next is 'less than 5', then 'Less than 10' . In such way you also can avoid using automation.

So, just decide which way is better for you. And Merry Christmas for you too!