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
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.
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.
What about fn!edate() for adding months?
if( ri!vfm_lastupdate>fn!edate(today(),60),"Testing Required", if( ri!vfm_lastupdate>fn!edate(today(),24),"Refurbished", "New" ) )
Edit: Didn't notice Stefan recommended edate() earlier :)