Return 0 instead of negative number

LeAndre P

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,

LeAndre P

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))

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))

LeAndre P

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

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))

LeAndre P

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

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

LeAndre P

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

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

@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".