if(ri!vfm_lastupdate<today()+730,"new",if(or((ri!vfm_lastupdate > today()+731),(ri!vfm_lastupdate>=today()+1440)), "refurbished",if(ri!vfm_lastupdate>today()+1828,"Testing Required","Null")))
here i need to check if the given date is less than 2 years compared to present date then show new, else between 2-5 display refurbished else more than 5 years display testing required
Discussion posts and replies are publicly visible
OK. And what is the result you see? The "or" in the second line looks suspicious.
in the above condition am adding number of days but instead i want to give no of years and check directly. in the or condition am checking between 2-5 years
One option: edate(today(), 24) will add 24 months.
What do you mean with "check directly"
given/entered date is less that 2 years compared to present date
Like this?
ri!vfm_lastupdate<edate(today(), 24)
It's usually easier to structure these things by ordering them in reverse order...test whether the value is > 2years first, then, if not, test the value is > than 1 year, then otherwise the value must be < 1 year.
Your requirement is:
Date < 2 years : condition will be "New" ,Date > 2years & date <5years - condition will be "refurbished", Date >5 years condition = "require testing"
So let's test if > 5 years first, then > 2 years second, otherwise it must be < 2 years.
Try to follow Stewarts advice.
And, writing "it's not working as per required " does no help me to understand what is going on!
Repeating my first post, the "or()" in the second line looks suspicious!
that's what how to give 2 years as in condition
This is how I would structure it:
if( ri!vfm_lastupdate>today()+1825,"Testing Required", if( ri!vfm_lastupdate>today()+730,"Refurbished", "New" ) )
You may disagree about the actual values (I've used 5 years as 365*5, but you may want to throw in a leap year value)...same as the value for 2 years (I've used 365*2) but the principle is the same.