Should be easy but.......

S

Steve

I have been racking my brains out, I have employees with senority that the
vacation is based on years employed.

If the employee has 1-3 years in, he gets 1 week vacation,
If the employee has 4-8 years in, he gets 2 weeks vacation.
If the employee has 9-15 years in, he gets 3 weeks vacation.
If the employee has 16-20 years in, he gets 4 weeks vacation
If the employee has 21-25 years in, he gets 5 weeks vacation

How can I make excel pick up the number of years worked and just print the
number of weeks vacation the employee would receive?

I know it can be done, I did it before, but I lost the spreadsheet and I
just can't remember how I did it.

Old age is sneeking up on me!!

Thanks Steve
 
C

cvolkert

All you need to do is set up a vlookup table. The first column of th
table will be the bottom threshold of each seniority grouping and th
second column will be the weeks of vacation. for each employee, yo
would just enter a vlookup like this:

=vlookup(A1, D1:E5,2)
where A1 = years of service
and D1:E5 is the table described above.

Since you don't tell the vlookup to find an exact match, it will selec
the value that is just less than A1 if there isn't an exact match.

Let us know if you have more questions, Cha
 
S

Steve

I don't know what I am doing wrong, I keep getting error messages when I
attempt to make the table as per your instruction. Belong is a sample of the
page I am trying to create, I did take out some of the important information
such as wages and last names etc.

When I refresh my data, (everything is imported in from another program,
into excel) everything updates, I would like to have the weeks of vacation to
also automatically update, this is the only column that I am beating my head
in over!

A B C
D
EMPLOYEE DATE STARTED NUMBER OF YEARS NUMBER OF WEEK VACATION
FLORENCIO 05/03/97 8 3
MANUEL 06/12/02 3 1
EDUARDO 04/04/05 0 0
PABLO 03/17/03 2 1
JUAN 09/26/00 5 2
FORTUNATO 08/02/96 9
JERRY 10/14/84 21
MARTIN 05/02/05 0
MARIO 05/24/04 1
JIMY 02/27/84 21
ROBERT 08/13/84 21
FRANK 06/21/04 1
ALAN 08/01/94 11

--
Thanks again for the reply, I do very much appreciate you taking the time
out of your day to help out.

Sincerely,

Steve
 
C

cvolkert

I've built an example file based on the information provided an
attached it hereto. Let me know if you have any other problems o
questions. Thanks, Cha

+-------------------------------------------------------------------
|Filename: Vacation.zip
|Download: http://www.excelforum.com/attachment.php?postid=4084
+-------------------------------------------------------------------
 
S

Steve

I think this is going to work!! Thank you so much, with all the employees,
and with the weeks changing every year, hopefully this will make printing out
the senority list and vacation list for the union and for the employees
faster and easier. Thanks Again.

Steve
 
M

Mariela

Hi - I know this is an old post but may be what I am looking for and I am
unable to capture the attachment. Can you help me get a copy of the zip file
so I can see if this helps me with my problem?
 
Top