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 ( )
–>2a) if the number of days between a record created and a field updated is 14 days or less, it returns an emoji ( ).
Is this possible?
Page 1 / 1
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
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?
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?
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'
)
)
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'
)
)
Thank you so much W_Vann_Hall. That’s incredibly helpful. I appreciate it.
While you’re my guardian angel, let me ask you this…
IF(Watched Field1 = “”,“ ”,“ ”) unless WatchedField2 and WatchedField3 and WatchedField4 have data in them, in which case, “ ”
Does that make sense?
While you’re my guardian angel, let me ask you this…
IF(Watched Field1 = “”,“ ”,“ ”) unless WatchedField2 and WatchedField3 and WatchedField4 have data in them, in which case, “ ”
Does that make sense?
I think I understand you:
IF(
NOT(
{WatchedField1}
),
'',
IF(
AND(
{WatchedField2},
{WatchedField3},
{WatchedField4}
),
'',
''
)
)
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},
‘ ’,
DATETIME_DIFF(
TODAY(),
CREATED_TIME(),
‘days’
)
)
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},
‘ ’,
DATETIME_DIFF(
TODAY(),
CREATED_TIME(),
‘days’
)
)
OK, see if this works:
IF(
{WatchedField},
'',
IF(
DATETIME_DIFF(
TODAY(),
CREATED_TIME(),
'days'
)<1,
0,
DATETIME_DIFF(
TODAY(),
CREATED_TIME(),
'days'
)
)
)
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 . If the date for {Next update} has passed I want 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.
IF(
IS_BEFORE(
TODAY(),
{Next Update}
),
“”,
“”
)
IF(
IS_BEFORE(
TODAY(),
{Next Update}
),
“”,
“”
)
Thank you so much for your help!
How would I go about modifying this one step further?
I have columns for date an invoice is made {Invoice date} and a date for last date for payment {Last date for payment}. In a new column I use the following folmula
IF(
{Invoice date}=BLANK(),
'', IF(
IS_BEFORE(
TODAY(),
{Last date for payment}
),
"",
""
))
No problems so far, but I would like to include that it checks if it’s payed or not in another column called {Payment date} (datefield).
If paid I want it to return .
How would I achieve this?
IF(
{Payment Date},
"",
IF(
{Invoice date}=BLANK(),
'', IF(
IS_BEFORE(
TODAY(),
{Last date for payment}
),
"",
""
)))
IF(
{Payment Date},
"",
IF(
{Invoice date}=BLANK(),
'', IF(
IS_BEFORE(
TODAY(),
{Last date for payment}
),
"",
""
)))
Thank you so much!
I swear that was the very first thing I tried, but admittingly I was a bit more tired than ideal when I tried it, so odds are I had a comma out of place or simmilar. Thank you!
I’m attempting to use something much like this for my personal airtable base when it comes to budgeting and tracking my income/expenses. I essentially am trying to put together the formula that states "If {Last Paid Date} is within X days ago, “Current”, but if longer than X days ago, “Past Due”. I thought I had it figured out but my formulas just don’t work. Any assistance would be greatly appreciated.
I’m attempting to use something much like this for my personal airtable base when it comes to budgeting and tracking my income/expenses. I essentially am trying to put together the formula that states "If {Last Paid Date} is within X days ago, “Current”, but if longer than X days ago, “Past Due”. I thought I had it figured out but my formulas just don’t work. Any assistance would be greatly appreciated.
Airtable now has a field type called “last date modified”. If you create a new field with that type you can select it to watch any field, or a specific one. You can then reference that in a formula field. In this example, “LastMod” is my “Last Date Modified” field. It watches another field in my table. The formula is first looks to see if there is a date in LastMod, and if there is continues to compare to a timeframe (greater than 3 minutes) and then enters the information.
)
).
',
”,“ 
”) unless WatchedField2 and WatchedField3 and WatchedField4 have data in them, in which case, “ 
’,
",
.