Number of days since field updated?

Lewis_Schiff · ‎Feb 28, 2018

I’m looking for some help with a formula that…

calculates the difference between the number of days since a record was created and a field has initially had content entered into it.
–> 1a) If field is blank, it would calculate the number days between record created and today
If the number of days between record created and field has initially had content put into it is greater than 14 days, it returns an emoji ( :stop_sign: )
–>2a) if the number of days between a record created and a field updated is 14 days or less, it returns an emoji ( :green_heart: ).

Is this possible?

W_Vann_Hall · ‎Feb 28, 2018

Well… It depends on what you put in the field…

Currently, Airtable does not support a ‘write once’ Formula field; that is to say, Formula fields are recalculated every time they are displayed. (The only exception to this rule exposed to the user is CREATED_TIME().) So the prospect of setting a ‘trigger’ with a formula along these lines —

IF({WatchedField}!=BLANK(),TODAY())

— won’t work, because Airtable determinedly lives in the present, where it’s always today…

That said, should you be fortunate enough that {WatchedField} is a Date field, you could implement the functionality you describe — as long as you can trust the timestamp.

Alternatively, if you can design your base so that {WatchedField} is a Linked Record field, and you can trust there to be a one-to-one correspondence between {WatchedField} and records in the linked table (that is, each {WatchedRecord} links to a single record in the linked table, and each record in the linked table links to no more than one {WatchedField}, you can calculate the Δ between the main record’s CREATED_TIME() and that of the linked record.

If you don’t need split-second accuracy — by which I mean you can live with plus or minus 20 minutes — this sounds like a perfect place to use Zapier: Define a view filtered to show records where {WatchedField} is not empty; configure Zapier to trigger on new records being added to the view; and have Zapier respond by entering the current date and time into a timestamp Date field. Since the record to be updated is the same one that triggers the Zap, it’s considered a two-stage Zap and can be executed from a free account

Lewis_Schiff · ‎Mar 01, 2018

Thanks very much for taking the time to respond. Unfortunately, there are many other fields that are entered manually so I think the zap would be firing a lot when it’s not relevant.

How about…

If {Watched Field} is empty, calculate and post the difference between the today and the record create date. If it’s not empty, post an emoji?

W_Vann_Hall · ‎Mar 01, 2018

You would trigger the zap based on the record first appearing in the view, so it would appear only when the target field first became non-blank.

IF(
    {WatchedField},
    '💖',
    DATETIME_DIFF(
        TODAY(),
        CREATED_TIME(),
        'days'
        )
    )

Lewis_Schiff · ‎Mar 01, 2018

Thank you so much W_Vann_Hall. That’s incredibly helpful. I appreciate it.

Lewis_Schiff · ‎Mar 01, 2018

While you’re my guardian angel, let me ask you this…

IF(Watched Field1 = “”,“ :red_circle: ”,“ :yellow_heart: ”) unless WatchedField2 and WatchedField3 and WatchedField4 have data in them, in which case, “ :green_heart: ”

Does that make sense?

W_Vann_Hall · ‎Mar 01, 2018

I think I understand you:

IF(
    NOT(
        {WatchedField1}
        ),
    '🔴',
    IF(
        AND(
            {WatchedField2},
            {WatchedField3},
            {WatchedField4}
            ),
        '💚',
        '💛'
        )
    )

Lewis_Schiff · ‎Mar 04, 2018

Thank you again, W_Vann_Hall. So, here’s the last challenge which is a function of combining your code suggestions:

Intended behavior:

If watched_field is empty, put in “0” immediately
If watched_field has been empty for more than 1 day since record was created, calculate the number of days it’s been empty
If watched_field has something in it, put in “smiley face” emoji"

This formula seems to work EXCEPT if there’s content in the field on the first day. In that case, it’s blank.

IF(
{Month Enrolled},
‘ :blush: ’,
DATETIME_DIFF(
TODAY(),
CREATED_TIME(),
‘days’
)
)

W_Vann_Hall · ‎Mar 04, 2018

OK, see if this works:

IF(
    {WatchedField},
    '😊',
    IF(
        DATETIME_DIFF(
            TODAY(),
            CREATED_TIME(),
            'days'
            )<1,
        0,
        DATETIME_DIFF(
            TODAY(),
            CREATED_TIME(),
            'days'
            )
        )
    )

AnnicaT · ‎Jan 12, 2019

Hi!
I’m looking for an adaptation of this.

I have two fields {Last updated} and {Next update}.
{Last update} is a manually updated date field, while {Next update} is an automatic calcualated date based on {Last update} and another field.

I wish to add a new field where one can easily see the update status.
If time for {Next update} have not yet come I want to display :green_heart: . If the date for {Next update} has passed I want :red_circle: to be displayed. I’ve been tinkering around a bit on my own, but as a beginner I keep running into errors. Any and all help would be greatly appreciated.