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
-
OOPs! (I can't seem to edit my original post) I meant:
If 'Date2' ≤ Today, then 'Expired'= "Yes"; else 'Expired' = "no"
-
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
-
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.
-
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.
-
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"
-
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"
-
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
-
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!
Content aside
- 6 yrs agoLast active
- 8Replies
- 3065Views