Discussions

Expand all | Collapse all

How to prevent negative values from being displayed

  • 1.  How to prevent negative values from being displayed

    Posted 17 days ago
    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
    ------------------------------


  • 2.  RE: How to prevent negative values from being displayed

    Posted 17 days ago
    Edited by Mark Shnier (YQC) 17 days 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
    ------------------------------



  • 3.  RE: How to prevent negative values from being displayed

    Posted 17 days ago
    Thank you Very much Mark!

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



  • 4.  RE: How to prevent negative values from being displayed

    Posted 17 days ago
    Edited by Austin K 17 days 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.


  • 5.  RE: How to prevent negative values from being displayed

    Posted 17 days 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
    ------------------------------



  • 6.  RE: How to prevent negative values from being displayed

    Posted 17 days ago
    Im actually getting an error:
     Sorry this is hard to read....
    Error in Formula


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



  • 7.  RE: How to prevent negative values from being displayed

    Posted 17 days ago
    PLease ignore... I figured out my issue with Marks Formula.

    missed the $ infront of the DaysFromEndDate Variable in the if statement

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