Should be easy but.......

[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
--
Steve

[cvolkert]

All you need to do is set up a vlookup table. The first column of the table will be the bottom threshold of each seniority grouping and the second column will be the weeks of vacation. for each employee, you 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 select the value that is just less than A1 if there isn't an exact match.

Let us know if you have more questions, Chad

[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


"cvolkert" wrote:

>
> All you need to do is set up a vlookup table. The first column of the
> table will be the bottom threshold of each seniority grouping and the
> second column will be the weeks of vacation. for each employee, you
> 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 select
> the value that is just less than A1 if there isn't an exact match.
>
> Let us know if you have more questions, Chad
>
>
> --
> cvolkert
> ------------------------------------------------------------------------
> cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
> View this thread: http://www.excelforum.com/showthread...hreadid=488964
>
>

[cvolkert]

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

[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
--
Steve


"cvolkert" wrote:

>
> I've built an example file based on the information provided and
> attached it hereto. Let me know if you have any other problems or
> questions. Thanks, Chad
>
>
> +-------------------------------------------------------------------+
> |Filename: Vacation.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4084 |
> +-------------------------------------------------------------------+
>
> --
> cvolkert
> ------------------------------------------------------------------------
> cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
> View this thread: http://www.excelforum.com/showthread...hreadid=488964
>
>