Date range formula

[lawsonhrconsultant]

Hi all

I'm not a formula person at all, just a crytal report user.

I'm trying to create a formula that will compute the years of service as it relates to a fiscal year.

For instance the fiscal year ends 06/30/02
Employee Joe was hired on 05/22/78.
Employee Joe has 24 years of completed service on June 30, 2002.

Any help would be appreciated.
Thanks in advance!

 

[HowardHammerman]

Try this:

For instance the fiscal year ends 06/30/02
Employee Joe was hired on 05/22/78.
Employee Joe has 24 years of completed service on June 30, 2002.
---------------------
whileprintingrecords;
datevar fyend := #6/30/2002#;
numbervar yrs := year(fyend) - year({table.hiredate});
numbervar mohire := month({table.hiredate});
numbervar dyhire := day({table.hiredate});
if date(year(fyend),mohire,dyhire) > fyend then
yrs := yrs -1;
yrs

Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[rosemaryl]

Dear Lawsonhrconsultant:

I am sure there are more elegant solutions and I tried to account for the fiscal year as a calculation rather than hard-coding it but it is a start for you.

datediff("YYYY",Date(1978,05,22),Date(2002,06,30))

You could replace the Hire date with the Actual Field that stores the employee's hire date:

//@completed_service

Stringvar Fiscal;
Fiscal := Totext(Year(CurrentDate),"####&quot + ",06" + ",30";


datediff("YYYY",{Table.Hiredate},Date(Fiscal))


Hope this helps,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Without variables, and constructing the text:

"Employee " + trim({YourTable.EmployeeFirstName}) +
", has " + totext(datediff("YYYY",{YourTable.EmployeeHireDate} , Date(year(datadate),6,30)),"#&quot + " years of service as of " + totext(Date(year(datadate),6,30))

I avoid using currentdate because if you generate this report, then look at it later through CE or with saved data, it will base the date calculation on the current date, not the datadate.

This *can* have it's advantages, meaning that you could base this report on currentdate vs. datadate, save it one time, then distribute it with data saved, then people could open it occasionally and it should be correct, providing the data hasn't changed back in the database.

But then the report itself is rendered useless as a snapshot in time.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Hmmm, I may be mistaken about the datadate<->currentdate historical instances, it doesn't appear to update in this example, I think that might have been an anomaly of a earlier version of SI.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com