0

trouble with formulas and dates.

I am a new user. Some things are intuitive but others are not. 

I have a 'Date' field.  I then used formulas to create a new 'Date2' field using Date+365.  This also has the Date format.

Now I want to have a field that is 'Expired' which would be "yes" if Date2 is before Today.  (More than 1 year has passed since 'Date') and "no" if less than a year has passed.  It would be nice to make it colored red too, but I don't need that.

I cannot figure out how to use the logic to accomplish this.  I want something like this:  

If 'Date2'-'Date' ≥ 0, then 'Expired'= "Yes"; else 'Expired' = "no"

8 replies

null
    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    OOPs!  (I can't seem to edit my original post)  I meant:

    If 'Date2' ≤ Today, then 'Expired'= "Yes"; else 'Expired' = "no"

    • Birger_H
    • 6 yrs ago
    • Reported - view

    Go get the Date in one year:

    –––
    'Date 2' := date(year(Date) + 1, month(Date), day(Date))
    –––

    To set 'expired' to "yes":

    –––
    'expired' := if 'Date 2' > today() then true else false end 
    –––

    Birger

    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    I tried your suggestion with no luck.  I first created the Numeric field Date2.   (I tried using a Date format field, but I can't see how to use a formula with that).  I entered what you suggested, and Ninox did not object to the formula, but when I tried to submit it, it returned the error "This formula may not alter data"?  I may be missing something more basic about how Ninox works.

    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    I have made some progress.  I put the formula you suggest in the "trigger on update" for the field 'Date'.  When I enter data into data, it produces output in 'Date2' which is also a Date formatted field. Yay!  

    The only strange thing is that Date2 is not one year from Data, but one year minus one day.  I can live with that, but I don't know why it does it.

    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    But now I am stuck again.  You gave me this formula:  'expired' := if 'Date 2' > today() then true else false end 

    But how do I implement it?  Do I first create a field called "expired"?  If so, what kind of field? (Text, Formiula, etc)

    And where do I put this formula?  I can't put it in the "trigger on update" field of either Date or Date2.  I want "expired" to update each day.  When I try to create a formula for "expired" it keeps telling me that  "This formula may not alter data"

    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    But now I am stuck again.  You gave me this formula:  'expired' := if 'Date 2' > today() then true else false end 

    But how do I implement it?  Do I first create a field called "expired"?  If so, what kind of field? (Text, Formiula, etc)

    And where do I put this formula?  I can't put it in the "trigger on update" field of either Date or Date2.  I want "expired" to update each day.  When I try to create a formula for "expired" it keeps telling me that  "This formula may not alter data"

    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    you can create a f(x) formula field "Expired". The formula:

    ---

    if date(year(Date) + 1, month(Date), day(Date)) < today() then
    styled("YES", "red")
    else
    styled("NO", "green")
    end

    ---

     

    Leo

    • snaprovmembership
    • 6 yrs ago
    • Reported - view

    Thanks to both Birger and Leo, this is now working as I wanted it to.  What I learned was in the formula: 

    'Date 2' := date(year(Date) + 1, month(Date), day(Date))

    I didn't actually have to use the    'Date 2' :=    part.  Perhaps I should have known this, but I did not.  Anyway, it works great now.  Thanks!

Login to reply

Content aside

  • 6 yrs agoLast active
  • 8Replies
  • 3067Views