Calculating Conditional End Dates

Hi there! Haven’t run this through too many scenarios to test… but check out the below example base (copy it by clicking the “Copy Base” icon in the top right corner to check out the formulas).

Basically, the field {RT Mod} looks at how many months have passed ({Months Passed}), and divides that by the Renewal Term (and adds one) to get how many times the Contract would have renewed since the original end date. Then the field {New End Date} adds the number of months in {RT}, multiplied by {RT Mod}.

You may need to adjust, but this should at least get you going in the right direction! Hope it helps. :slightly_smiling_face:

Thank you! After looking it over it was the:

IF({OG End Date},
    IF(IS_AFTER(TODAY(), 
        {OG End Date}), 
        CEILING(DATETIME_DIFF(TODAY(), {OG End Date}, 'months'))
    )
)

That I was missing. I really appreciate the work and I have been testing the formula. I have weeded down the fields into one new field and would like your opinion on the way I have it laid out.

IF({OG CED},
    IF(IS_AFTER(TODAY(), {OG CED}), 
        DATEADD(
            {OG CED}, 
            VALUE({Renewal Term}) * SUM(
                CEILING(
                    DATETIME_DIFF(
                        TODAY(), 
                        {OG CED}, 
                        'months'
                    )
                ) / VALUE({Renewal Term}), 1
            ), 
            'month'
        )
    )
)

In the end I would like to have two fields. One for the OG CED and one for the New CED.
Thank you again for the quick help! You’re awesome.

EDIT: I revised the formula. I could not get the AND() to work in the outermost IF()

IF({OG End Date},
    IF(IS_AFTER(TODAY(), 
        {OG End Date}), 
        CEILING(DATETIME_DIFF(TODAY(), {OG End Date}, 'months'))
    )
)

IF({OG CED},
    IF(IS_AFTER(TODAY(), {OG CED}), 
        DATEADD(
            {OG CED}, 
            VALUE({Renewal Term}) * SUM(
                CEILING(
                    DATETIME_DIFF(
                        TODAY(), 
                        {OG CED}, 
                        'months'
                    )
                ) / VALUE({Renewal Term}), 1
            ), 
            'month'
        )
    )
)

I think I know why. Were you trying to structure it like this?

IF(AND({OG CED}, IS_AFTER(TODAY(), {OG CED})), ...

If so, you were probably getting #ERROR results on fields with no dates, correct?

Many programming languages will process the logical elements fed to AND and OR operators sequentially, only going as far as they need to in order to determine the outcome. Take AND for example. It will only return TRUE if all logical elements are true, so in some languages, the processor will stop testing elements the moment it finds one that returns FALSE, knowing that it can safely return FALSE.

Unfortunately Airtable isn’t like that. It tests everything fed to AND(). What it looks like you want to happen by using AND() is to automatically make that outer IF() evaluate to FALSE if there’s no date in the {OG CED} field, and not even bother running the IS_AFTER() test in such cases. Sadly, IS_AFTER() will always run no matter what, and will generate the error when it doesn’t find a date. That’s why you have to break up the tests into nested IF() functions.

IF(AND({OG CED}, IS_AFTER(TODAY(), {OG CED})), ...

That is exactly what happened. It makes sense, I’m glad I’m not crazy! There are a few other conditions That I need to check for so the formula still needs tweaking but you got me 90% of the way there! I will rework the IS_AFTER check and hope for the best.