Forum Discussion

JennyMarshall's avatar
JennyMarshall
Qrew Trainee
6 years ago

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

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.
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    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.