DURATION FORMULA - need results past expiration date to display as 0 instead of negative

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Current Formula:

If([Expiration Date] - Today()<=0, 0,[Expiration Date] - Today())

Syntax Error:

The expression [Expiration Date] - Today() on the left hand side of the operator "<" is of type duration while the expression 0 on the right hand side is of type number. The operator "<" cannot be used with these types of expressions.


What I want:

Display the days until certificate is expired. If days expired is less than or equal to 0, display 0. If days expired is greater than 0, display days until expired.

i.e.   expiration date of 7/15/2016 from today 7/6/2016   displays 9 days

        expiration date of 6/15/2016 from today 7/6/2016   displays 0 days

        (not -21 days) 

Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
If(

[Expiration Date]<=Today(),Days(0),

[Expiration Date] - Today())




Try the above.

Jack



(*Amended error to include brackets)
Photo of mkosek

mkosek

  • 918 Points 500 badge 2x thumb
I think we're close. here is the syntax error I get : Formula syntax error

The argument list of the function Today must begin with a left parenthesis.



If(
 [Valid Until]<=Today  ,Days(0),
 [Valid Until] - Today())
Photo of Jack

Jack, Champion

  • 50 Points
Apologies, if forgot the brackets:

If(

[Expiration Date]<=Today(),Days(0),

[Expiration Date] - Today())
Photo of Jack

Jack, Champion

  • 50 Points
You could also add in an is null in case no date is entered:

If(
IsNull([Expiration Date]),Days(0),

[Expiration Date]<=Today,Days(0),

[Expiration Date] - Today())