NETDAYS formula returning negative number

LeAndre P

Hello,

I am working with a formula that calculates the number of years remaining between two dates. Several of the date have long passed and are returning a negative number. I would like all date results retuning negative numbers to simply return as zero. What changes are needed to the current formula to achieve this goal?  

=NETDAYS(TODAY(), [Warranty Expiration Date]@row) / 365

Thank you,

Darren Mullen

See here:

https://help.smartsheet.com/function/netdays

Start date comes first, end date comes second, so if Warranty Expiration is in the past, it will be negative. If that is your desired result, then your formula is fine. If you want to return a zero, then you'd need an if statement to check if the result of your formula is negative or less than zero. If so, then you would just have the if statement display a zero, otherwise the result of your formula.

Darren Mullen

See here:

https://help.smartsheet.com/function/netdays

Start date comes first, end date comes second, so if Warranty Expiration is in the past, it will be negative. If that is your desired result, then your formula is fine. If you want to return a zero, then you'd need an if statement to check if the result of your formula is negative or less than zero. If so, then you would just have the if statement display a zero, otherwise the result of your formula.

LeAndre P

The formula itself seems to be working as expected however, when I add the IF statement, it returns incorrect argument. 

LeAndre P

I believe I figured it out. After several attempts I ended up with the following formula.

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