Skip to main content
Question

I need a formula that says if the [Term} = 0 or N/A then Expiration Date = N/A, otherwise AdjustMonth([Term],{Effective]).

  • April 12, 2019
  • 0 replies
  • 35 views
I want a formula that calculates the expiration date based on the effective date plus the term unless the term is N/A or 0 (I can do wither one), if the term is N/A or 0 then the expiration date is N/A.
      This topic has been closed for replies.

      • AustinK
      • Registered
      • April 12, 2019
      It depends what your field types are. I assume Term is a text field since you can have 0 or N/A in it. Might be better off going with a numeric field and treating blank as a 0. I am going to go with it being a text field though. There might be a better way to do this but this works.

      I would just make your "Expiration Date" field a formula date and put this in it. If term = blank then Expiration date is just blank. If it has a term then it adjusts the date based on that term. 

      If([Term]="", ToDate(""), AdjustMonth([Effective], ToNumber([Term])))

      I think you might be better off making it so that instead of going by if term is 0 have a checkbox or something that makes it explicit that there is no term. Then this formula would not have to interpret what a user might randomly put into a text box.
      Terms & ConditionsAccessibility statement