Calculate length of employment

[LaRetta]

This should be simple, but I can't make it work ...

Premise: I need to know how many years an employee has worked for us, to calculate their monthly benefits. From 1 yr to 10, they get a certain number of vacation days (1=1, 2=2, etc. After 10 years, they get the flat 10 year vacation benefit (10 days). I want the number of vacation days they get, to be in a field, so if the 'benefit package' changes, a user can change the 'formula' easily witout redefining the field formula.

I can't even get the formula to work to calculate number of years employed. Geeeezzzz. Both dates are date fields. Vacation benefit day is numeric. And, how do I specify to stop calculating at 10 years and remain constant? I apologize for being so ignorant. I've already wasted over 10 hours trying to figure this out and researching it.

[rdhaden]

Subtracting one date from another gives you the number of days between them, so one example of how you might do this is:

Vacation Days (calculation, number) =

If(Truncate((End Date - Start Date) / 365, 0) >= 10, 10, Truncate((End Date - Start Date) / 365, 0))

[LaRetta]

It works perfectly -- thanks so much! Does this take into account leap year? For some reason, in my old Access days, I thought there was something else I need to take into account when I was calculating the current age of a child but I don't have access to that old db. But this sure gives me what I asked for!

[rdhaden]

No, what I gave you doesn't work with leap years. If I used it to calculate my age, it would say I will be turning 31 eight days before my birthday. Here's one that works even with leap years because it's comparing the month, day, and year of each date rather than using the number of days in a year.

Case( Year(End Date) > Year(Start Date) and Month(End Date) > Month(Start Date),

Year(End Date) - Year(Start Date),

Month(End Date) < Month(Start Date), Year(End Date) - Year(Start Date) - 1,

Day(End Date) >= Day(Start Date), Year(End Date) - Year(Start Date), Year(End Date) - Year(Start Date) - 1)