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!