• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

calclulate Employee Vacation days

Employee Vacation days:
Looking for a way to calculate employee vacation days left based on hire date and length of employment.
Condition: If employed for 6 month then entitled vacation days = 5, if more than 2 years then 6 days, more than 3 years then 7 days, more than 4 years then 8 days, more than 5 years then 9 days and if more than 10 years then 10 days.
I would like to calculate above for all employees with a click of a button.
2 Solutions
You would create a query that would query a table with the emplyees Date of employement. Then you would use the date of employement to find the number of days worked and then run a simple If statement. If Days worked  >= 36500 (10 years) = 10 and so on.
nibirkhanAuthor Commented:
thanks ctpmn1 for your idea, I need a little bit more details, note that i am going to calculate for all the employees in one shot. I do know the idea as you mentioned but I need code or query statement.
Jeffrey CoachmanMIS LiasonCommented:
<I do know the idea as you mentioned but I need code or query statement.>
First, despite it's apparent simplicity, this is not a simple task.

First, most companies calculate Vacation in Hours, not days.
Which make these calculations much simpler.
(Suppose someone takes 1 hour off to get married and wants it used as Vacation?)

You also have the issue of recalculating the exact number on the anniversary of the hire date.
Most Date functions in Access (ex. DateDiff() ) will "round" their values.

So again, it is not so simple to do this.

Here is a sample that simply takes the Difference between the Employees HireDate and Today's Date (in months and years) to calculate Vacation days.
(Using the DataDiff, in a function)
As I said, it may not be perfect, but it does work, and shows you the basics of how your specific system can be built.
You will have to study this carefully and adjust it where needed.

See the query: qryVacDays
(You can make a Report or a Form from this query)


Rey Obrero (Capricorn1)Commented:
place this function in a regular module

in a Query, create something like this, assuming the hire date field is named HireDate

select [Name],HireDate,ComputeVacationDays([HireDate]) as DaysOfVacation
From EmployeeTable

(* change field name and table name accordingly)

save the query as QVacations

in a click of a button

Private sub Button_click()
docmd.openquery "Qvacations"
end sub

Function ComputeVacationDays(datHire As Date)
If VarType(datHire) <> 7 Then ComputeVacationDays = Null: Exit Function
Dim hDate As Date, varYear As Long
hDate = DateSerial(Year(Date), Month(Date), Day(datHire))
varYear = DateDiff("yyyy", datHire, Date) + (Date < hDate)
If varYear > 0 Then
    Select Case varYear
        Case Is >= 10: ComputeVacationDays = 10
        Case Is >= 5: ComputeVacationDays = 9
        Case Is >= 4: ComputeVacationDays = 8
        Case Is >= 3: ComputeVacationDays = 7
        Case Is >= 2: ComputeVacationDays = 6
        Case Is >= 1: ComputeVacationDays = 5
    End Select
    varYear = DateDiff("m", datHire, Date) + (Date < hDate)
    Select Case varYear
        Case Is >= 6: ComputeVacationDays = 5
        Case Else: ComputeVacationDays = 0
    End Select
End If
End Function

Open in new window

Rey Obrero (Capricorn1)Commented:

take note about HireDate with day the same as the day you are going to run the Query values

2/19/2010  > is entitled to 5 days, starting today 8/19/2010

the function i posted computes the vacation days considering Day, Months and Years.. of HireDate

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now