Solved

calclulate Employee Vacation days

Posted on 2010-08-19
5
471 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:nibirkhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:ctpmn1
ID: 33479705
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.
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 33479965
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.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 33481607
<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)

;-)

JeffCoachman
Access-EEQ26416074Calculate-Vaca.mdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 33481612
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
    Else
    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

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33481663
nibirkhan,

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

0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question