Using today as an argument in a formula

Hi Sam

Something like this should do it:

IF(Date >= TODAY(), “Rounds”, “Deadline”)

and as seen in the formula window:

Hope this helps?

Julian

Thank you SO much!

When I looked at what you wrote, I said to myself, “I TRIED that!!!” but I clearly did since that worked. Like I said in my OP, I think I just got so inside the problem that I frustrated myself too much instead of being able to take a step back (not to take anything away from your help!)

I really appreciate your assistance!

No problem - we’ve all been there!

Hi Sam

Wondering if you could perhaps help as I am struggling with a similar issue.

I want to write and IF Function thats says

IF actual start date is greater than the planned start date return “Delayed Start”
AND IF actual start date is blank and today’s date is after the planned start date then return “Delayed Start”

Can this be done?

I think you meant “OR IF actual start date is blank…”

I haven’t tested it, but I think this should work:

IF(
    OR(
        IS_AFTER(
            {Actual Start Date},
            {Planned Start Date}
            ),
        AND(
            NOT(
                {Actual Start Date}
                ),
            IS_AFTER(
                TODAY(),
                {Planned Start Date}
                )
            )
        ),
    'Delayed Start'
    )

Not sure i am inputting it correctly. Tried copying and pasting directly but it doesnt seem to work.

Somehow, it appears the single quote after 'Delayed Start' has become a ‘curly quote’ rather than a straight quote; Airtable formulas require the latter. Try re-typing that one character from your keyboard.

That odd character seems to be the problem — but there’s another issue with the original formula I provided: For some reason, it returns #ERROR whenever {Actual Start Date} is blank. I couldn’t get it to work after a couple of attempts, so I rewrote the whole algorithm into a more ungainly one that does work. (This version also doesn’t return #ERROR as its default response until a date is entered.)

IF(
    {Actual Start Date},
    IF(
        IS_AFTER(
            {Actual Start Date},
            {Planned Start Date}
            ),
        'Delayed Start'
        ),
    IF(
        IS_AFTER(
            TODAY(),
            {Planned Start Date}
            ),
        'Delayed Start'
        )
    )

still no luck. I had to play with the spacing a bit so that might be the issue

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.)

This seems to work:

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…

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

Thanks for your help, will give that a go.