x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 213

# Automatic Calculation

I'm developing a product for my HR department that will calculate vacation time and such based on date of hire all beginning after 6 months and will vary on the length of time the employee has been here.

I'm having problems figuring out some of the logic.  Say I enter a employee who's hire date is today which would be less than six months.  An entry would be created in the database for vacation used as 0.

Now, the problem comes in how I check each employees record every day and when it comes time for their 6 month or 2 year anniversary to update the record with the appropriate amount of time.

Any help on this logic would be greatly appreciated.
0
• 3
1 Solution

Commented:
Hello,

Actually it is better to store hiring date and then perform simple calculations.

DayOf6Month = DateSerial(Year(HireDate), Month(HireDate) + 6, Day(HireDate))

Sincerely,
Crin
0

Commented:
Of course then you will be able to find how many days, months or years one or another employee is here.

DaysCount = Now - HireDate
MonthsCount = (Year(Now) - Year(HireDate)) * 12 + _
(Month(Now) - Month(HireDate))

Additional advantage of this approach is that software should not perform operations every day (just suppose computer will be off one day and every employee will lose one day of the employment history).

Sincerely,
Crin
0

Author Commented:
Thanks, I'll give that a try.....
0

Commented:
Ouch, forgot to send YearsCount formula:

Dim YearsCount as Double
YearsCount = DaysCount / 365.25

Also, for more precise MonthsCount formula you can get this one (previous calculated integer months):

Dim FirstMonthPart as Double
Dim LastMonthPart as Double
Dim MonthsCount as Double

FirstMonthPart = (DateSerial(Year(HireDate), Month(HireDate) + 1, 0) - HireDate) / _
(DateSerial(Year(HireDate), Month(HireDate) + 1, 0) - DateSerial(Year(HireDate), Month(HireDate), 1))

LastMonthPart = (Now - DateSerial(Year(Now), Month(Now), 1) - Now) / _
(DateSerial(Year(Now), Month(Now) + 1, 0) - DateSerial(Year(Now), Month(Now), 1))

MonthsCount = (Year(Now) - Year(HireDate)) * 12 + _
(Month(Now) - Month(HireDate)) + _
FirstMonthPart + LastMonthPart

Sincerely,
Crin
0
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.