InstructionWhich7142
Registered User.
- Local time
- Today, 09:29
- Joined
- Feb 24, 2010
- Messages
- 203
So I used to do this by year but it was "bumpy" because if some high value thing suddenly loses 20% of it's value in one month of the year it looks higher than the other 12, it was suggested to do it by 1.66% per month instead, I ended up coming up with this abomination (i'd already written my own MAX and MIN for using in queries (happy to have a better suggestion here, I was just sick of having to nest iif just to get max/min)
I basically convert dates to months in the epoch, subtract the old date from today's date, push it back 60 months to get the "more than 5 years old" then divide that remainder by 60, the MIN and MAX just solve the negative bit that occurs within 5 years and the greater than 60 bit after 10 years
(1-Round(xMin(xMax((((CLng(Format(Now(),"yyyy"))*12+CLng(Format(Now(),"mm")))-(CLng(Format([everyday],"yyyy"))*12+CLng(Format([everyday],"mm")))-60)/60),0),1),3))
Hope there's an elegant way i've missed
I basically convert dates to months in the epoch, subtract the old date from today's date, push it back 60 months to get the "more than 5 years old" then divide that remainder by 60, the MIN and MAX just solve the negative bit that occurs within 5 years and the greater than 60 bit after 10 years
(1-Round(xMin(xMax((((CLng(Format(Now(),"yyyy"))*12+CLng(Format(Now(),"mm")))-(CLng(Format([everyday],"yyyy"))*12+CLng(Format([everyday],"mm")))-60)/60),0),1),3))
Hope there's an elegant way i've missed
Last edited: