Service Anniversary calculation

[pshimkus]

Hi,

I'm running Crystal 11 and need to do a simple calculation. I need to show when an employee's service date or birthdate is within 7 days of running the report. I need to use only the Month and Date not the year. Any Date Difference formula I can think of use the year.

This should be simple.

Thanks,

Peter

 

[kray4660]

I just tested the datediff formula in XI (I believe it has been about the same for several versions), and it shows the difference in days (example: datediff("d",{birthdate}, currentdate()) )

 

[briangriffin]

There are probably several ways to do this, but in essence you want to make the year of both the service date and the birthdate fields the same as the current year.

whileprintingrecords;
datevar v_servicedate;
datevar v_birthdate;

v_servicedate := dateadd("yyyy",(year(currentdate) - year({servicedatefield})),{servicedatefield});
v_birthdate := dateadd("yyyy",(year(currentdate) - year(birthdatefield})),{birthdatefield});

if datediff("d",currentdate,v_servicedate) <= 7 or datediff("d",currentdate,v_birthdate) <= 7 then true else false

 

[briangriffin]

Oops, the datediff fields are reversed - should be:

if datediff("d",v_servicedate,currentdate) <= 7 or datediff("d",v_birthdate,currentdate) <= 7 then true else false

 

[pshimkus]

OK, thanks. If both dates were in the same year, it is easy. Taking into the different years is what threw me.

Thanks!

Peter

 

[pshimkus]

I ran into a problem when you are at the end of the year but need the dates from the start of the new year, Dec 30 to Jan 4 for example.

I created a formula, @START DATE, to determine the difference:

Whileprintingrecords;
datepart ("y", {DATE_FIELD}) - datepart("y",currentdate)

This give the days between today and the starting date.

Then I used this to supress the details section when that difference is outside the 7 day window. The second check is for when the anniversary date is in the beginning of the new year. The final check is to eliminate the previous 7 days.

(abs({@START DATE}) > 7 and abs({@START DATE}) <358) or {@START DATE} < 0

Thanks!

Peter