Good Afternoon:

I have a perplexing calculation that I would like help with, if possible.

I am working on a large Human Resources Database using FM 7 Pro. I am trying to calculate years of service for an employee. I have the field calculating current date - hire date divided by 365 which returns the value fine. What if the employee leaves? I have a field for termination as a yes/no value list and then a field for termination date. I need my database to stop calculating years of service if the terminated field is set to yes. I have tried "IF" statements calculating current-hire date/365 if the value of the terminated field is "no" and another statement current date-termination date/365 if the value in terminated is "yes". It does not work. I also tried a case statement but cannot seem to get that to work either. Can anyone assist me? Many thanks in advance.

You can omit your current date field. You want an unstored (calculation, type is date) with:

If ( termDate ; Year ( termDate ) ; Year ( Get ( CurrentDate ) ) ) - Year ( hireDate )

... that should do it for you. You didn't indicate whether months were taken into account or days - and whether you wish to round up or down. This calc is VERY generic. We can adjust as needed because if someone works 10 months, what do you consider their years of service? We need rules here.

Could you post your actual calculation. We may spot something in it that's not quite right. Thanks.

Thanks for the advice. I was trying to store it as a numeric calculation with one decimal point to indicate year and months of service.

I will give your method a shot.

Many thanks.

We can provide a more accurate calculation including months of service. This calc assumes they don't get one year credit until they have been there a full year. It all depends upon your requirements. But it sounds like you've got it now. :wink2:

Man thanks, the command worked perfectly. I did have to store the data as a number rather than date. When I made that change, it worked great!