Forum Discussion

JerrettKnaub1's avatar
JerrettKnaub1
Qrew Member
6 months ago

Complex Formula Solution Help

I'm having difficulty developing a formula text field that displays a Status on a coming up report that are due. Currently the reports could be due Monthly, Quarterly, or Annuel. I have the following field to collect information: Reporting Monthly Start Date, Reporting Quarterly Start Date, Reporting Yearly Start Date, Reporting Submitted Date. The Submitted Date field idea is to track the date which the report was submitted. The other start dates would be the starting due date of the report, if they are due monthly, quarterly, or annual.

The idea is to have the Status be displayed on a QuickBase report showing "Soon Due" or "Due" when a certain duration is met from the next due date. Such as if due Monthly, then Status would be "Soon Due" 14 days before next due date and all the way till the due date status, "Due" which would be on the day before it is due for a 1 day buffer. This would be cleared if the Reporting Submitted Date field clears and the report is current.

Any advice on setting this formula for this to work? 

Thank you in advance,

Jerrett



------------------------------
Jerrett Knaub
------------------------------

3 Replies

Sort By
  • MarceloBenavide's avatar
    MarceloBenavide
    Qrew Cadet
    6 months ago

    Hello. Just an  Idea: If (Today() >= [montlhyStartDate] - Days(14) , "Soon due", "good")   , I'm didn't testing, check the sintax or others issues.

    Thank you

    Marcelo BeEnavides Torres



    ------------------------------
    Marcelo Benavides
    ------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    6 months ago

    Are all of those fields different and are you actually setting them when you create the reporting entry? I'm going to interpret from the way you described it that the Monthly/Quarterly/Yearly date are all different and a report would have only one of those values. If that's correct, then you really only have to make the formula figure out which date to use. 

    So like: 

    var date nextDueDate = if( 

         not isnull([Annual Date]), [Annual Date],

         not isnull([Quarterly Date]), [Quarterly Date],

         not isnull([Monthly Date]), [Monthly Date],

    null);

    if( 

         not isnull( [Reported Submitted Date]), "Submitted",

         ($nextDueDate - Today()) <= Days(1), "Due",

         ($nextDueDate - Today()) <= Days(14),"Soon Due",

    "Not Due")

    Something like that. Again - my assumption is that you have a date already populated in one of your annual/quarterly/monthly date fields. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • JerrettKnaub1's avatar
      JerrettKnaub1
      Qrew Member
      6 months ago

      Thank you very much Chayce, the formula works amazing!



      ------------------------------
      Jerrett Knaub
      ------------------------------