Ignore negative: Calculate time between dates ignore negative numbers

latheobald89201
latheobald89201 ✭✭✭
edited 12/09/19 in Formulas and Functions

I am running a formula to calculate the days between a start date and today(), if the number is less than 25 then it inserts today's date in a cell.  That cell is then used to trigger an email to a user for action.

If the start date is in the past the calculated days between is a negative.  I want the formula to ignore the number if it is a negative and not trigger the email to the user.  ie if the date is in the past don't insert todays date.

=IF([Departure Date]83 - TODAY() < 25, TODAY(), 0)

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use something as basic as saying if your current formula is greater than 0, run the formula.

     

    =IF(Current Formula > 0, Current Formula)

     

    =IF(IF([Departure Date]83 - TODAY() < 25, TODAY(), 0) > 0, IF([Departure Date]83 - TODAY() < 25, TODAY(), 0))

  • Hi Paul,

    I have a similar use case, but in this case I'm accounting for the formula to manage through an error if the division in the formula is occurring between two 0's, then the value is No Growth.

    =IFERROR(([Column6]78 - [Column5]78) / [Column5]78, "No Growth")

    I'd like to expand up on this formula and also return "No Growth" if the growth is a negative number. I tried a couple of different formulas but I'm not successful. Any suggestions/recommendations? thanks, Monica

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!