Improve company productivity with a Business Account.Sign Up

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

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
baddogi
Asked:
baddogi
  • 3
1 Solution
 
CrinCommented:
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
 
CrinCommented:
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
 
baddogiAuthor Commented:
Thanks, I'll give that a try.....
0
 
CrinCommented:
Ouch, forgot to send YearsCount formula:

Dim YearsCount as Double
YearsCount = DaysCount / 365.25

which will adequatelly take care about leap-years.

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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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