Return 0 instead of negative number

LeAndre P
LeAndre P ✭✭
edited 08/30/21 in Formulas and Functions

Hello,

I have a formula that calculates the number of years between contract dates and a second formula to calculate time remaining. The time remaining formula works as expected before reaching and passing the date of expiration within the formula. Currently, when the date of expiration is either met or have past, the formula returns the number of days after the date of expiration as a -negative number. When this happens I would like to have a 0 return instead of a negative number.

This is the formation I am currently using:

=(ABS(TODAY() - [Warranty Expiration Date]@row) / 365.25)

 

Thanks in advance,

Best Answer

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓

    Thanks Paul

    the following formula seems to work as expected..

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), 0))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The ABS function should not be outputting a negative number. If it is then try this...

    =IF(ABS(TODAY() - [Warranty Expiration Date]@row) / 365.25) < 0, 0, ABS(TODAY() - [Warranty Expiration Date]@row) / 365.25))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks Paul unfortunately, I am getting #UNPARSEABLE when I tried the formula.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry about that. Mixed up the parenthesis. Try this...

    =IF(ABS((TODAY() - [Warranty Expiration Date]@row) / 365.25) < 0, 0, ABS((TODAY() - [Warranty Expiration Date]@row) / 365.25))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you Paul I am no longer getting the #UNPARSEABLE error msg however, the formula does not seem to zero out when the expiration date is met and or exceeded. For example, the contract period shown below should reflect zero considering the exp. date was 33 days ago.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try swapping the dates so that you are subtracting today from the expiration date instead.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • LeAndre P
    LeAndre P ✭✭
    Answer ✓

    Thanks Paul

    the following formula seems to work as expected..

    =IF([Warranty Expiration Date]@row > TODAY(), NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365, IF([Warranty Expiration Date]@row < TODAY(), 0))

  • Sarah123
    Sarah123 ✭✭✭✭
    edited 09/09/22

    I'm trying to do the same type of thing - if my if statement produces a negative number than return a 0. I've tried putting < 0,0 but it's not working (maybe I'm putting that in the wrong section of the formula?).


    =IF(COUNT(CHILDREN(Project@row)) > 0, SUM(CHILDREN()), ([Hours per Role]@row - [FY22 W46 RH]@row - SUMIF({Person}, Resources@row, {FY22 W46})))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah123 Where exactly are you trying to put it? Here's the basics...


    (Examples used below)

    Original formula:

    =SUM(CHILDREN())


    New formula:

    =IF(SUM(CHILDREN())< 0, 0, SUM(CHILDREN()))


    Basically you need to repeat the formula by saying if it is less than zero then output a zero, otherwise output the formula.


    You can also use a MAX function.

    =MAX(SUM(CHILDREN()), 0)


    If the original SUM(CHILDREN()) drops below zero then the zero becomes the "MAX". If it goes over zero then the SUM(CHILDREN()) becomes the "MAX".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!