Calculating Time

[bowks14]

Hi Everyone,

I am making a database that tracks Employee's vacation/personal/sick days. I want to be able to automatically calculate accrual rates based on the time a specific employee has been working here. For example, our organization accrues 0.84 vacation days per month if you have worked here 0-5 years, 1.25 days vacation per month for 6-10 years, 1.67 days vacation per month for 11-20 years, and 2.083 days per month for anyone who has worked 21+ years.

I have two fields, start date and vacation accrual rate. I want vacation accrual rate to automatically fill in as 0.84, 1.25, 1.67, or 2.083 depending on the date and update itself, daily. So for instance, if my start date was 06/01/2005, my accrual rate should automatically say 0.84 and on 06/01/2010, it should automatically change to 1.25.

Is this possible?

Thanks in advance!

 

[raskew]

Hi -

Copy/paste to a standard module and call it as shown.

You may need to adjust the fAccrualDate line, depending on your interpretation of the cutoff points. As written, 0-5 means thru
1 day short of 6 years, since the next category is 6-10.

Function fAccrualDate(pHireDate As Date) As Single
'*******************************************
'Purpose:   Return a leave accrual rate
'           based on full number of years
'           served
'Coded by:  raskew
'Inputs:    1) ? fAccrualDate(#6/21/95#)
'           2) ? fAccrualDate(#6/21/85#)
'Output:    1) 1.67
'           2) 2.083
'*******************************************

Dim StartDate As Date
Dim LvCat As Integer
Dim AcRate As Single
 
   StartDate = pHireDate
   LvCat = DateDiff("m", StartDate, Date) + (DateSerial(year(Date), month(StartDate), Day(StartDate)) > Date)
   Debug.Print LvCat
   fAccrualDate = Switch(LvCat < 72, 0.84, LvCat < 132, 1.25, LvCat < 252, 1.67, LvCat >= 252, 2.083)

End Function

The + (DateSerial(year(Date), month(StartDate), Day(StartDate)) > Date is a boolean statement that returns -1 if True, 0 if False.
This is necessary since the DateDiff() function compares Years without regard to months and days, Months without regard to days.
Example: ? datediff("yyyy", #12/31/07#, #1/1/08#) returns 1 (year) when in fact only 1 day has elapsed.

HTH - Bob