Using today as an argument in a formula

Sam_Soon · ‎Apr 19, 2017

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.

Julian_Kirkness · ‎Apr 19, 2017

Hi Sam

Something like this should do it:

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

and as seen in the formula window:

Hope this helps?

Julian

Sam_Soon · ‎Apr 19, 2017

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!

Julian_Kirkness · ‎Apr 19, 2017

No problem - we’ve all been there!

Douglas_Roberts · ‎Jul 30, 2018

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?

W_Vann_Hall · ‎Jul 30, 2018

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

Douglas_Roberts · ‎Jul 30, 2018

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

W_Vann_Hall · ‎Jul 30, 2018

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.

W_Vann_Hall · ‎Jul 30, 2018

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

Douglas_Roberts · ‎Jul 30, 2018

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