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

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

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.
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!

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".
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!