Return 0 instead of negative number
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

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

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

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

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

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.

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

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

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

@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".
Help Article Resources
Categories
Check out the Formula Handbook template!