0

I am using QuickBase which utilizes HTML. I am trying to create a code 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] March 2021 >= August 2022 , FALSE ..... June 2021 >= August 2022 , FALSE ... ... eventually getting to ... September 2022 >= August 2022, TRUE Posted in the field would be 9/1/22

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

Shane Miller
  • 1
  • 1
  • Have you tried to create this yourself? Maybe try a different approach: calculate the amount of months between your static and created date. Then divide the difference of months by 3 (your increment) and round your result down. Now you'll have the amount of months that fit. Add those months to the static date and you'll have your target date. – Emiel Zuurbier Jul 20 '22 at 16:15
  • Hey Emiel, thank you for your response! I am more familiar with SQL, however, I am trying to solve this problem which unfortunately requires HTML. This question is by far the most difficult thus far and I have hit a road block. I like your approach. I am open to any ideas that lead to the correct solution. Would you be able/ willing to write up a HTML code for your recommendation? – Shane Miller Jul 20 '22 at 16:33
  • Alright. Do you mean a JavaScript solution? Because HTML is for markup and can't do any calculations. Your required solution depends on your situation, meaning how is your data exposed, can you make the calculation on the frontend (your browser) or on the backend (your server). You know what I mean? I could write you something, but at this point I have no clue if it would help you out. – Emiel Zuurbier Jul 20 '22 at 18:43

1 Answers1

2

Typically in Quickbase you would be doing this kind of calculation with a formula field. Assuming that's the case, you can use a formula like this:

var Number dateCreatedMonths = 12 * Year(ToDate([Date Created])) + Month(ToDate([Date Created]));
var Number staticDateMonths = 12 * Year([Static Date]) + Month([Static Date]);

AdjustMonth([Static Date], 3 * Ceil(($dateCreatedMonths - $staticDateMonths) / 3))

The above assumes that you're using it in a new Formula - Date field and that the Static Date field is of type Date. The formula gets the difference in months between the Date Created and Static Date fields, divides that difference by 3 and rounds up using Ceil to get the minimum number of three month increments required to make the MM/YYYY of Static Date greater than or equal to the MM/YYYY of Date Created. Finally, the AdjustMonth function is used to add those months to Static Date to get a new date value.

Nathan Hawe
  • 281
  • 3
  • 5