Discussions

 View Only
Expand all | Collapse all

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

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

    Posted 07-20-2022 11:37
    Edited by Shane Miller 07-20-2022 13:25
    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 than or equal to MM/YYYY of [date created]. Once the above has been marked as true, I would like that manipulated static date displayed in the field type on the form.

    Example: [static date = 1/1/2021], [date created = 8/1/22]
    April 2021 >= August 2022 , FALSE ..... July 2021 >= August 2022 , FALSE ...
    ... eventually getting to ...
    October 2022 >= August 2022, TRUE
    Posted in the field would be 10/1/22

    Thank you in advance to the highly intelligent being that figures this out

    ------------------------------
    Shane Miller
    ------------------------------


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

    Posted 07-20-2022 12:00
    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
    ------------------------------



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

    Posted 07-20-2022 12:58
    Edited by Shane Miller 07-20-2022 13:18

    Hey Mark, 
    I appreciate you responding to my post. Essentially I have to create a manual report in excel for a wide range of companies on a quarterly basis. The quarterly months for each unique company is derived from the [static date AKA effective contract date]. These static dates are all in the past, some as far back as 2020. The static dates can be any given month, which is where the complication arises.
    I would like this formula to calculate and display when the next quarterly report is due based on the [static date AKA effective contract date]. However, I don't want it to display a date that is before the [Date Created AKA QuickBases standard field that is automatically applied to a table]. 

    Thus, in response to your question, if I am understanding correctly, I don't believe "date created plus 1 month, else blank" would work in this scenario. The displayed date will vary based on the [static date] and [date created]. Does that make sense? It is difficult for me to articulate, but I'll do my best.

    So if report 1 has a [static date AKA effective date] of 2/1/2022, and the [date created AKA date report 1 is added into quickbase table] is 8/1/2022, I want quickbase to display the date of 8/1/22. (not to insult intelligence, just to fully explain and get on the same page --->) If the [static date AKA effective date] is 2/1/2022, I would need to write a report quarterly on dates: 5/1/22, 8/1/22, 11/1/22, and 2/1/23. Thus, why I need to include the [date created] is so that the formula identifies the first one of those quarterly dates that is equal to or greater than [date created] and then display it.

     



    ------------------------------
    Shane Miller
    ------------------------------



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

    Posted 07-20-2022 13:05
    I should also mention the Field Label and Type:

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

    ------------------------------
    Shane Miller
    ------------------------------



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

    Posted 07-20-2022 14:36
    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
    ------------------------------



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

    Posted 07-20-2022 14:47
    Yes! I am 99% sure that is the question we are trying to solve for! Looking forward to your answer!

    ------------------------------
    Shane Miller
    ------------------------------



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

    Posted 07-20-2022 15:08
    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
    ------------------------------



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

    Posted 07-20-2022 15:14
    Edited by Shane Miller 07-20-2022 15:16
    This is awesome! I high hopes this will work.
    I am getting an error for the line:
    Min(
    *expecting text but found date*
    any ideas on how to fix?

    ------------------------------
    Shane Miller
    ------------------------------



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

    Posted 07-20-2022 15:18
    :) ..... Well the result of the calculation is the Quarterly Date so just change the field type to formula date.

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



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

    Posted 07-20-2022 15:27
    You sir, are a scholar and a gentleman. My brain thanks you!

    ------------------------------
    Shane Miller
    ------------------------------