year_diff formula help 1

[agray123]

I requested help on this issue last year

thread149-394624

and thought the formula:
Local NumberVar year_diff := DateDiff("yyyy",{MIGRATE_DIVISION.REVIEW_DT}, CurrentDate)



If Currentdate > {MIGRATE_DIVISION.REVIEW_DT} then
DateAdd('yyyy', {@year_diff} +1, {MIGRATE_DIVISION.REVIEW_DT})
else
{MIGRATE_DIVISION.REVIEW_DT}

worked great in the waning months of 2002, when all review dates had passed and all future review dates were not an issue.

Now I am running into a probelm when , for instance I have review date of 3-2003 as the true nextdate....it is showing on the report as 3-2004.

HELP

 

[mbarron]

If I read everything correctly, you want anything less than today to show the same month and day, but with the year being 2004, otherwise, you want the date itself.

If that's the case, this should work.

datevar review:={MIGRATE_DIVISION.REVIEW_DT} ;
datevar out:
If review < currentdate then
out:=date(year(currentdate)+1,month(review),day(review) else
review Mike
If you're not part of the solution, you're part of the precipitate.

 

[agray123]

I am getting an error when I run this formula that everything after datevar out: is useless text...

 

[synapsevampire]

Just some typos, try this:

datevar review:={MIGRATE_DIVISION.REVIEW_DT} ;
datevar out;
If review < currentdate then
out:=date(year(currentdate)+1,month(review),day(review))
else
review

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[agray123]

Thanks for clearing up the syntax, I am still getting the wrong dates.

Let me break it down for you like this:
Our system will set review date to one year after hire date.
It will not automatically update, so I have folks hired in March 1979 with review dates of March 1980. I am trying to get around this.

Should I just use a Month() to get the month of the review date, then also use Year() to get a year. Then write a formula that looks not only at the hire year but the month, so if hire year is 2003, with a month of July, it will only show a review date of July 2003 until August, when it runs to July 2004?

This logic should also apply to a hire month of March with a year of 1979, next review will be March 2003 until April, then it is March 2004.

I would appreciate any assistance.

 

[mbarron]

This should do it.

datevar review:={MIGRATE_DIVISION.REVIEW_DT} ;
datevar revadj;
datevar cdadj;
datevar out;

revadj:=date(year(today),month(review),1);
cdadj:=date(year(currentdate),month(currentdate),1);

if review > currentdate then out:=review else
If revadj < cdadj then
out:=date(year(currentdate)+1,month(review),day(review)) else
out:=date(year(currentdate),month(review),day(review));
out Mike
If you're not part of the solution, you're part of the precipitate.

 

[agray123]

In the words of Eric Cartman....

SWEEEEET

Thanks!