Forum Discussion

MannyPinheiro1's avatar
MannyPinheiro1
Qrew Trainee
6 years ago

How to prevent negative values from being displayed

Hello

I am working on a Table field where I am calculating the number of days that have passed since the date entered in another date field "Date of Release". 

This formula seems to work great:

var date EndDate = Today();
ToDays($EndDate - [Date of Release])

However, if the date is in the Future, the formula is returning a negative value - which is ok, but I would like to suppress negative values from being displayed in the field and only display positive values greater than 0 returned by the formula.

Any suggestions?

------------------------------
Manny Pinheiro
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    try this

    var date EndDate = Today();
    var number DaysFromEndDate = ToDays($EndDate - [Date of Release]);

    IF($DaysFromEndDate>=0, $DaysFromEndDate)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MannyPinheiro1's avatar
      MannyPinheiro1
      Qrew Trainee
      6 years ago
      Thank you Very much Mark!

      ------------------------------
      Manny Pinheiro
      ------------------------------
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    Mark beat me to it but since I already took the time to test it..

    You can do it very simply by just adding max to your formula. This will show a 0 for anything in the future and the actual days taken for anything in the past. You can also get into doing this for workdays as well but you didn't mention needing that.

    var date EndDate = Today();
    max(0,ToDays($EndDate - [Date of Release]))

    You could also do it in a way that didn't show 0 or a blank but instead showed text of some sort, like "Not Started" "Work Pending" or whatever you wanted. It would have to be a text field instead of numeric though, which may or may not work for you.
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      Austin's formula will return a 0, and mine will return a blank, if negative.  So it depends if you want to see a blank or a zero when negative.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • MannyPinheiro1's avatar
        MannyPinheiro1
        Qrew Trainee
        6 years ago
        Im actually getting an error:
         Sorry this is hard to read....


        ------------------------------
        Manny Pinheiro
        ------------------------------