Years of Service formula error 3

[neronikita]

Good morning!

I am trying to use the following formula in Crystal 2008.

(CurrentDate - {Employee.DOH}) /365.25

Employee.DOH is in the date format.

It is telling me it needs a number at the employee.DOH field. no matter what I try to convert it to, it just tells me that it needs to be some kind of number or date. I'm not sure what the problem is and I'm a little rusty on my Crystal formulas. I can't figure out what it really wants from me.

I am using SQL Server 2008 for my database.

Thanks,

Di

 

[IdoMillet]

Try (CurrentDate - cDate({Employee.DOH})) /365.25

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Madawc]

Also DateDiff is an easier way to get a difference in years, among other functions. Use HELP to get the basics and experiment a bit till you're familiar with it. Saves a lot of time once you know.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[neronikita]

Thanks to both of you. I used to know this stuff years ago, but it's been a while since I worked with it. The cDate worked, and I will play with DateDiff.

Thanks again!

Di

 

[neronikita]

Quick question -

I have two formulas calculating two different Years of Service dates for employees who have worked here more than once. When I do a third formula to add them together, it only adds the ones with two formulas (and not all employees have two sets of dates) so the employees who only worked here once do not show anything. Any thoughts on why it only shows when there are two sets of dates?

Thanks,

Di

 

[lbass]

Please show the contents of both formulas. It sounds like you have fields that can be null, e.g., history fields, so you would have to do a check for this.

-LB

 

[neronikita]

Formulas are:

oldYOS
((cdate({Employee.PreviousDOT})-cdate({Employee.PreviousDOH}))/365.25)

YOS
((CurrentDate - cDate({Employee.CurrentDOH})) /365.25)

TotalYOS
({@oldyos} + {@yos})

Yes, the previousDOT and PreviousDOH allow nulls.

Thanks,

Di

 

[lbass]

Change the first formula to:

if isnull({Employee.PreviousDOT}) or
isnull({Employee.PreviousDOH}) then
0 else
(cdate({Employee.PreviousDOT})-cdate({Employee.PreviousDOH}))/365.25

-LB

 

[neronikita]

That works perfectly! Thanks lbass!

Di