Show same date in current year from another date

[sarah2michelle]

I am sorry the subject is confusing, but I'm not sure how to summarize the question better.

We have a date that could be during the current year or anytime prior. I need to get that date in the last 12 months. If the date was 12/1/2005, that's within the past 12 months, so it is good. If it is 7/30/2005, I would need to return 7/30/2006 instead.

I know that other people have to be doing this, does anyone have a pointer to a similar formula or anything else that might help?

Thanks,
Sarah

 

[sarah2michelle]

I should have messed with this longer before posting it. Here is the formula I came up with, if anyone has better ideas, they would be very much appreciated! This logic does not take leap years into account.

iif(DateDiff("d",{@hiredate},CurrentDate)/365>=1,{@hiredate}+(truncate(DateDiff("d",{@hiredate},CurrentDate)/365)*365),{@hiredate})

 

[synapsevampire]

Your formula doesn't allow for leap years, so it could produce bad results and evaluate improperly.

if cdate(year(currentdate),month({@hiredate}),day({@hiredate})) > currentdate then
dateserial(year(currentdate)-1,month({@hiredate}),day({@hiredate}))
else
cdate(year(currentdate),month({@hiredate}),day({@hiredate}))

-k