Crystal reports 2008 - continual service date

[Former Member]

Hi

I really need some help with a report i am putting together. I need to know the employees continual service in years, months and days. It is stored on the database as just complete years but i need it to display in the reports as yy/mm/dd so that i can run reports for those with less than 6 months, those with 6 to 12 months, those with 12 - 24 months etc etc.

I have used the formal field to do this (as i would to covert a DOB in to an age) and it works except all the figures are exactly a month out. I have asked it take todays date and take away the continual service date. Today's date is correct on the system and the continual service date on the database is also correct. Why is it a month out? I can't think of any other way to get these reports but there must be a way.

Please could someone get in touch to let me know how to do this?

Thank you.

[Former Member]

It's easy enough to do w/ a few formulas...

years:

IF DatePart("m",
IF DatePArt("d", {Employee.Hire Date}) <= DatePart("d", CurrentDate)
THEN {Employee.Hire Date} ELSE DateAdd("m", 1, {Employee.Hire Date})
) > DatePart("m", CurrentDate)
THEN
DateDiff("yyyy", {Employee.Hire Date}, CurrentDate) - 1
ELSE 
DateDiff("yyyy", {Employee.Hire Date}, CurrentDate)

months:

IF DatePArt("d", {Employee.Hire Date}) > DatePart("d", CurrentDate)
THEN
DateDiff("m", DateAdd("yyyy",{@years},{Employee.Hire Date}), CurrentDate) - 1
ELSE 
DateDiff("m", DateAdd("yyyy",{@years},{Employee.Hire Date}), CurrentDate)

days:

DateDiff("d", DateAdd("m", {@months}, DateAdd("yyyy", {@years},
 {Employee.Hire Date})), CurrentDate)

formatted:

(IF LEN(ToText({@years},"0")) =1 
THEN "0" & ToText({@years},"0") ELSE ToText({@years},"0"))
& "/" & 
(IF LEN(ToText({@months},"0")) =1 
THEN "0" & ToText({@months},"0") ELSE ToText({@months},"0"))
& "/" & 
(IF LEN(ToText({@days},"0")) =1 
THEN "0" & ToText({@days},"0") ELSE ToText({@days},"0"))

HTH,

Jason

[Former Member]

Hi Jason, thank you very much for such a quick responce. I have pasted all of these in the formula that i have created called 'contnual service' but it says that there is an error. Do i need to create seperate formulars for the years, months and dates? and then apply them all?

[Former Member]

yup... 4 in all.

[Former Member]

I did that but it's not worked

[Former Member]

The first 3 formulas need to be named EXACTLY as they are in my example... They are being referenced by each other and the final formula... The "formatted" formula can be named however you want.

Also... Make sure you are replacing the {Employee.Hire Date} with the actual field from your database.

Jason

[former_member11696]

Hello,

Sorry this is not a case management system but a community forum for all to use. If you need immediate assistance then please log a case on line:

http://store.businessobjects.com/store/bobjamer/DisplayProductByTypePage&parentCategoryID=&categoryI...

Or be patient while someone looks over your issue.

Thank you

Don