Setting up formula's using dates?

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Here's my scenerio.
Fields are:
DateHired
VacationAllowed
VacationTaken
VacationAvailable
HourlyEmployee?

Hourly employees get 1 week vacation (5 days) after their
first year anniversary hire in date.

Salary employeess get 1 week after their first year
anniversary hire in date. 2 Weeks after 2nd year.

Can anyone help me out? I'd sure appreciate it!!
 
Where do you want to do this: in a query, or in a module?

In a query, you could have a calculated field like:

VacationAllowed: IIf([HourlyEmployee?], IIf(DateAdd("yyyy", 1, [DateHired])
<= Date(), 5, 0), IIf(DateAdd("yyyy", 2, [DateHired]) <= Date(), 10,
IIf(DateAdd("yyyy", 1, [DateHired]) <= Date(), 5, 0)))

Watch for word-wrap: that's all on one line.

This assumes the HourlyEmployee is a boolean field that's true for hourly
employees, and false for salary. What I'm doing to Hourly employees is
adding 1 year to their DateHired, and checking whether it's already passed.
If so, they get 5 days vacation, otherwise they get nothing. For non-hourly
employees, I first add 2 years to their DateHired, and check whether it's
already passed. If so, they get 10 days. If not, I add 1 year to their
DateHired and chekc whether it's already passed. if so, they get 5 days.
Otherwise, they get nothing.
 
Back
Top