ShaneMiller1
2 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
------------------------------

### 9 Replies

• 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@gmail.com
------------------------------
• 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
------------------------------
• I should also mention the Field Label and Type:

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

------------------------------
Shane Miller
------------------------------
• 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].

------------------------------