Forum Discussion

ShaneMiller1's avatar
ShaneMiller1
Qrew Cadet
3 years ago

Formula that incrementally adds 3 months to a [static date] until it is greater than or equal to [date created].

Hello!  I am trying to create a field that essentially looks at a [static date] (which will always be in the past), and have it add on 3 months incrementally until the MM/YYYY eventually is greater t...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
Shane, are you asking for this.

When the Static Date plus 3 months is greater than the Date Created show the Date Created plus 1 month, else blank?

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
ShaneMiller1's avatar
ShaneMiller1
Qrew Cadet
3 years ago
I should also mention the Field Label and Type:

Date created: Date/ Time
Static date AKA effective contract date: Date

------------------------------
Shane Miller
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I'm not sure of the answer yet, but let's see if we can boil down the question to fewer words.

    Calculate the next quarterly date greater than or equal to [Date Created] based on a repeating cycle of quarterly dates based on the [Effective Contract date]. 

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ShaneMiller1's avatar
      ShaneMiller1
      Qrew Cadet
      3 years ago
      Yes! I am 99% sure that is the question we are trying to solve for! Looking forward to your answer!

      ------------------------------
      Shane Miller
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        OK, try this. add as many Q's as you might ever need.

        var date DateCreated = ToDate([Date Created]);

        var date QZero = AdjustMonth([Contract Effective Date],0);
        var date QOne = AdjustMonth([Contract Effective Date],3);
        var date QTwo = AdjustMonth([Contract Effective Date],6);
        var date QThree = AdjustMonth([Contract Effective Date],9);
        var date QFour = AdjustMonth([Contract Effective Date],12);
        var date QFive = AdjustMonth([Contract Effective Date],15);

        Min(
        If($QZero >= $DateCreated, $QZero),
        If($QOne >= $DateCreated, $QOne),
        If($QTwo >= $DateCreated, $QTwo),
        If($QThree>= $DateCreated, $QThree),
        If($QFour >= $DateCreated, $QFour),
        If($QFive >= $DateCreated, $QFive))

        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------