Annual Leave Calculator

[NoviceIan]

Hi,

My programming skills are non existent and I need to write a small visual
basic procedure for our staff database. Basically we need to calculate the
amount of annual leave a staff member is entitled to.

This is worked out on the WTE (whole time equivalent) and the start date.
Full WTE works out as 262.5 hours a year. Then there are additional hours
for length of service.

Over 5 Years extra 15 Hours (On Basic)
Over 10 Years extra 45 Hours (On Basic)

Only like I mentioned my programming skills are non existent and I'm not
sure how or where I'd even incorporate the function.

Please could somebody offer me some advice on this issue, many thanks

Ian

 

[Duane Hookom]

How about providing some details on your tables and fields? We don't even
know where/if you are storing a hire date and/or WTE. It would also help if
you provided a variety of sample hire dates and WTE with the properly
calculated values.

 

[NoviceIan]

Sorry here are some details:

We store WTE and Start Date in the staff table.
All annual leave is stored in the absence table using Leave date, Return
Date and Hours Missed.

Here are some examples of Leave Entitlement:

WTE Start Date Entitlement

1.00 01/06/2004 Standard entitlement 262.5
1.00 01/06/2000 Standard + >5 yrs bonus (15hrs) = 277.5
1.00 01/06/1991 Standard + >10 yrs bonus (45hrs) = 307.5
0.40 01/06/2004 0.40 of Standard = 105
0.40 01/06/2000 0.40 of Standard + 0.40 of >5 yrs bonus (6hrs) = 111
0.40 01/06/1991 0.40 of Standard + 0.40 of >10 yrs bonus (18hrs) = 123

262.5 is standard leave for 1.00 WTE.
Any additional hours are based solely on length of service.

Ian

 

[Duane Hookom]

You might want to create a small function that calculates leave entitlement:

Function GetLeave(datStart As Date, dblWTE As Double) As Double
Dim intYears As Integer
Dim dblHours As Double
intYears = DateDiff("yyyy", datStart, Now()) + _
Int(Format(Now(), "mmdd") < Format(datStart, "mmdd"))
dblHours = 262.5 'standard
Select Case intYears 'bonus
Case Is > 10
dblHours = dblHours + 45
Case Is > 5
dblHours = dblHours + 15
End Select
dblHours = dblHours * dblWTE 'equivalent
GetLeave = dblHours
End Function

You can then use this function anywhere that you would use other functions.
Save the above function in a module named "basEmployeeCalcs".