Calculating Conditional End Dates

Brady_Menegay · ‎Dec 27, 2019

I am trying to create a field that will pull from 3 other fields to generate a contract termination date.

Each account has a install date (ID), contract term (CT), and renewal term (RT). For example:

ID=1/1/2017
CT=36 months
RT=12 months

Calculating the initial end date is easy (ID + CT) but I am stuck on the renewal. I want to be able to incrementally add months to the term length depending on whether or not the end date (CED) has already passed. For example:

SD=12/1/2016
TL=36 months
CED=12/1/2019 (original end date)
RT=12 months
CED=12/1/2020 (actual end date once the original has passed)

I have a formula adding the renewal term to the original contract (ID + CT + RT) but cant figure out how to add RT as many times as needed to make the CED greater than today IF(CED<=TODAY(), CED++).

Any advice would be appreciated, THANKS!

AlliAlosa · ‎Dec 27, 2019

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:

Brady_Menegay · ‎Dec 27, 2019

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

Justin_Barrett · ‎Dec 27, 2019

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.

Brady_Menegay · ‎Dec 27, 2019

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.