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 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
------------------------------
  • 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

      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
      ------------------------------
    • ShaneMiller1's avatar
      ShaneMiller1
      Qrew Cadet
      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
        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
        ------------------------------