Using today as an argument in a formula

[Sam_Soon]

I’ve been searching the forums and the help topics for two days. I’m sure the information is here, and, at this point, I’m just too frustrated to find it. I’m asking you to please not simply link me to the information but to also provide the information in a reply. TIA.

My team is migrating from to-do app to AirTable for a variety of reasons, but one of the (many) ways we are using AirTable is as an extremely glorified to-do list. One base that we use has a “Deadline” field. Another field lists various records as either a “Round” or a “Deadline” (the field is called “Type”).

We are looking for a formula that will give us this: if the “deadline” field is before today, then the “type” field will automatically be changed from “rounds” to “deadline” – dates that are equal to or greater than today would remain as “rounds” (Background: When a record is entered, it is automatically entered as "rounds.)

Every time I’ve tried formulas such as those I would use in Excel or similar to examples I’ve found here, I got error messages essentially telling me that “today” was not a recognised category.

Again, TIA.

[W_Vann_Hall]

No, both the first and second formulas I provided are valid formulas; the first just has that annoying #ERROR! problem I can’t clear.

Your second example seems to suggest your field names don’t match what I provided — you’ll have to change them to match your naming convention. (You’ll notice you did fix the first issue: Airtable now sees it as a valid formula and attempts to parse it, at which point it discovers it doesn’t understand the field names.)

[Julian_Kirkness]

This seems to work:

IF(OR({Actual Start Date}>{Planned Start Date},AND({Actual Start Date} = BLANK(),{Planned Start Date}<TODAY())),‘Delayed Start’,BLANK())

[Douglas_Roberts]

Julian_Kirkness:

IF(OR({Actual Start Date}>{Planned Start Date},AND({Actual Start Date} = BLANK(),{Planned Start Date}<TODAY())),‘Delayed Start’,BLANK())

Genius - Thank You.

Is there a way to update a field based on an input to another?

I am trying to update my planned start field based on the value input into the actual end field for a previous task…

[Julian_Kirkness]

Hi Douglas

This is probably not so easy - formula fields operate within a single record and I am assuming that your looking to calculate a value based on a value in a different record in the same table?

If this is the case, then it’s likely that you won’t be able to do it just in Airtable but would need something external like Zapier or Integromat - the latter is my favourite despite being a bit clunky when building logic for Airtable at the moment.

Data wise, you would probably need to create a link from your task table to itself and then link one record to another. Normally this would mean you could use Lookup fields and formulae to do what you need but when Self Joined, you can’t do this - hence the logic needing to be external.

Julian

[Douglas_Roberts]

Thanks for your help, will give that a go.