Formula based on two dates

[Joanne_Wong]

I’ve been trying to figure out if I can create a formula around two date fields…
The situation:
Create a field with the possible outcomes as SOLD, PENDING, FIRM, CANCELLED depending on two date fields.

SOLD if Today is before Signing Date
PENDING if Today is currently equal to or after Signing Date and before Firm Date.
FIRM if Today is equal to or after Firm Date
CANCELLED (I have no clue how to incorporate this. Basically, I still need a status for deals that have been cancelled, but I don’t know how this would tie in with dates…)

Nevermind, I think I might have figured out how to do this… I added an extra checkmark column for the CANCELLED records

IF({FIRM DATE FX}=BLANK(),"",IF({CANCELLED?}=1,“CANCELLED”, IF(
IS_AFTER({SIGNING DATE copy},TODAY()),
“SOLD”,
IF(
AND({SIGNING DATE copy}<=TODAY(),{FIRM DATE FX}>TODAY()),
“PENDING”,
IF({FIRM DATE FX}<=TODAY(),“FIRM”,"")
)
)
))

Any help would be greatly appreciated. I had this formula working in Excel, but the functionalities on Airtable are a bit different. I used IFS function for it in Excel and for the records with the CANCELLED status, we had just manually typed in CANCELLED in the cell (obviously won’t work here on Airtable since the field is a formula)

[Justin_Barrett · Accepted solution]

Welcome to the community, @Joanne_Wong! :grinning_face_with_big_eyes: Your formula looks like it should work, though it could be optimized slightly.

You can test for the filled/non-filled state of (almost) any field by just referencing the field name. Any data in the field is equivalent to True, while an empty field is equivalent to False (I said “almost” earlier because number fields have issues with this, but you’re not dealing with numbers, so we can skip over that caveat for now :slightly_smiling_face: )

You can also omit the third argument in an IF() function and the output will default to the proper null/empty/blank value to match the second argument.

With those small tweaks, your formula looks like this:

IF(
    {FIRM DATE FX},
    IF(
        {CANCELLED?},
        "CANCELLED",
        IF(
            IS_AFTER({SIGNING DATE copy}, TODAY()),
            "SOLD",
            IF(
                AND({SIGNING DATE copy}<=TODAY(), {FIRM DATE FX}>TODAY()),
                "PENDING",
                IF(
                    {FIRM DATE FX}<=TODAY(),
                    "FIRM"
                )
            )
        )
    )
)

Those changes wouldn’t change the usability of your original formula, though. Is it not working? If so, what exactly is misbehaving?