New to Excel with Question

  • Thread starter Thread starter PaulStroik
  • Start date Start date
P

PaulStroik

I am new to Excel and thought this a good opportunity to learn something
about Excel. The Project I am working on requires figuring out the
Vacation hours earned after being employed X number of years. If
present year minus year of employment is greater than (or less than) X,
then hours earned equals Y. I hope this is clear enough!? Any direction
would be appreciated. So far I have been using the Help File but have
much to learn about Formulas.
Thank you for any assistance.
 
Hi!

Here's one way.

Assume the vacation ranges are:

1 yr...........40 hrs
5 yrs.........80 hrs
10 yrs.....120 hrs
20+ yrs...160 hrs

Employee hire date is in cell A2:

=VLOOKUP(YEAR(TODAY())-YEAR(A2),{1,40;5,80;10,120;20,160},2,1)

If the date difference ( in years) is less than 1 yr the formula will return
#N/A. Any difference in years greater then 20 will return 160.

That answers your question as posed. However, just about every employer has
their own method for determining vacation earned. Some base it on the
employees hire date anniversary.

Consider this example:

Employee hire date: 12/31/2004

Todays date: 1/1/2005

Using this example the above formula would return 40 even though the total
difference in dates is only 1 day.

Also, if an employer bases vacation time on anniversary dates how does an
employee take 40 hrs of vacation starting on 12/31/2005? Hopefully, some
common sense is applied but you never can tell with some companies!

Biff
 
Back
Top