Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Cadet
26 days ago

How to Dynamically Check for Renewal Dates in the Next Month in QuickBase

Hi QuickBase Community,

I'm trying to create a formula field that checks if any of several renewal dates (in various fields) are in the next month (from the current date). I want this formula to dynamically calculate the next month without hardcoding specific months.

I have several date fields, including:

  • TMB Renewal
  • BLS Renewal
  • ACLS Renewal
  • PALS Renewal

I want the formula to check if any of these fields have a renewal date that falls in the next month. For example:

  • If today is December, the formula should check for renewal dates in January.
  • If today is August, it should check for renewal dates in September, and so on.

I am not great at the code part of Quickbase and all my attempts so far have failed. 

Thank you for your help!

formulas
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    26 days ago

    Your fundamental tools are  AdjustMonth, FirstDayOfMonth and LastDayOfMonth.

    If( [TMB Renewal] <= LastDayOfMonth(AdjustMonth(Today(),1)) and  

    [TMB Renewal] >= FirstDayOfMonth(AdjustMonth(Today(),1)), true, false )

    This checks of the TMB Renewal is in next month and will dynamically change every month.

     

    • MeaganMcOlin's avatar
      MeaganMcOlin
      Qrew Cadet
      26 days ago

      Don,

      Thank you so much for your reply. I tried that code in a formula text field, is that correct? And I received this error. "Expecting text but found bool"

      Also if I get this working can I add the other date fields in the same formula? I am hoping to see all monthly due-dates across all renewal fields. 

      • DonLarson's avatar
        DonLarson
        Qrew Elite
        26 days ago

        The formula I gave you results in a True or False answer to the question, "Is it Due Next Month"  and specifically the TMB Renewal.  That field type should be Formula Checkbox.

        If you just want to see the dates you can make a Formula Text Field but need to change the outputs something like this.

        If( [TMB Renewal] <= LastDayOfMonth(AdjustMonth(Today(),1)) and  

        [TMB Renewal] >= FirstDayOfMonth(AdjustMonth(Today(),1)),  ToText(

        [TMB Renewal] ), "" )

        This would show the date if it is next month and nothing if it is not.  There are unlimited options.  I have a client that has field like this that checks on supplier payments.  All the logic is about 400 rows.   You should not need anything that long.