Calculate Years of Service to # of Vacation Days

[Kitty1234]

I believe there is a simple formula for this but I can't figure it out.

here's my dilemma:

I have a list of employees with various years of service.
The number of vacation days they receive is based on their years of service.
1-2 years = 10 days
3-7 years = 15 days
8-18 years = 20 days
19-24 years = 25 days
25 years = 30 days (they get an extra week in their 25th year)
26-29 years = 25 days
30+ years = 30 days

I want to be able to enter the # of years of service in one column and have another column that calculates how many vacation days they have.
There are some other things that i'll want to add (just to complicate it!)
later. Our vacation resets in June. When someone starts working after June
they get one day for each month of work until the following June. ie. a
November start would result in 7 days of vacation. How would I factor
this in?

anyway, this is for a rush job (as they all are, i'm sure) so your
quick response will be much appreciated.

Thanks so much! This site will save me!

 

[gcue]

For the first part of the equation, you can use VLOOKUP.

You will start by entering your criteria in 2 columns. 1 column with the tenure and the other with the # of vacation days associated with the tenure:

First Column:

1
3
8
19
25
26
30

Adjacent column (to the right of the first)

10
15
20
25
30
25
30

Then select all cells containing your input. Then go to insert > Name > Define. Type in the name of the selected cells. I used the name Vacation.

You can hide the cells that have this info so its not visible to the user.

Then in the cell where you want to calculate the vacation, type:

=VLOOKUP(D2,Vacation,2)

in this case, D2 is the cell that contains the number of years info for the employee. You can change dependent on the location of that info on your spreadsheet. Vacation is the reference to the criteria you had entered. The 2 makes reference to the second column of info in range "Vacation"

You can copy and paste the formula after entering it in one of the cells.

I hope this helps/is enough to get you started.

Bobby