I am trying to automatically calculate vacation hours. I have the employees
hire date (column D), their years of service (Column E) which is calculated
by subtracting todays date by their hire date and put into numbers, and then
I have their vacation time allotted (Column F). Here's how the hours go:

1 year but less than 3 gets 80 hours
3 years but less than 15 gets 120 hours
15 years but less than 25 gets 160 hours
25 years and over gets 200

I tried making criteria under tools, name, define and making a string like:
If(AND(E61,E6<3),80,IF(AND(E63,E6<15),120)) and it seemed to be working
for a couple cells but on others it rendered False.
Also, this will be for different spreadsheets so on some I will have only 10
employees but on others I will have many more. If there a way to type in
criteria that would copy down the cells of employees so that whatever their
Years of service equals their vacation hours would equal this? I've built
databases in Access but I'm unfamiliar with putting code in Excel. Any help
is appreciated.

Assuming E6 contains a number like 2 or 12 and represents the years of service:
In F6 for example:
=if(E6<1,0,if(E6<3,80,if(E6<15,120,if(E6<25,160,20 0))))

Then select the cell and drag fill down the column.

--
Regards,
Tom Ogilvy


"Pixie78" wrote:

I am trying to automatically calculate vacation hours. I have the employees
hire date (column D), their years of service (Column E) which is calculated
by subtracting todays date by their hire date and put into numbers, and then
I have their vacation time allotted (Column F). Here's how the hours go:

1 year but less than 3 gets 80 hours
3 years but less than 15 gets 120 hours
15 years but less than 25 gets 160 hours
25 years and over gets 200

I tried making criteria under tools, name, define and making a string like:
If(AND(E61,E6<3),80,IF(AND(E63,E6<15),120)) and it seemed to be working
for a couple cells but on others it rendered False.
Also, this will be for different spreadsheets so on some I will have only 10
employees but on others I will have many more. If there a way to type in
criteria that would copy down the cells of employees so that whatever their
Years of service equals their vacation hours would equal this? I've built
databases in Access but I'm unfamiliar with putting code in Excel. Any help
is appreciated.

Perfect! Thank you so much! One less thing I have to try to remember to
calculate on a constant basis.

"Tom Ogilvy" wrote:

Assuming E6 contains a number like 2 or 12 and represents the years of service:
In F6 for example:
=if(E6<1,0,if(E6<3,80,if(E6<15,120,if(E6<25,160,20 0))))

Then select the cell and drag fill down the column.

--
Regards,
Tom Ogilvy


"Pixie78" wrote:

I am trying to automatically calculate vacation hours. I have the employees
hire date (column D), their years of service (Column E) which is calculated
by subtracting todays date by their hire date and put into numbers, and then
I have their vacation time allotted (Column F). Here's how the hours go:

1 year but less than 3 gets 80 hours
3 years but less than 15 gets 120 hours
15 years but less than 25 gets 160 hours
25 years and over gets 200

I tried making criteria under tools, name, define and making a string like:
If(AND(E61,E6<3),80,IF(AND(E63,E6<15),120)) and it seemed to be working
for a couple cells but on others it rendered False.
Also, this will be for different spreadsheets so on some I will have only 10
employees but on others I will have many more. If there a way to type in
criteria that would copy down the cells of employees so that whatever their
Years of service equals their vacation hours would equal this? I've built
databases in Access but I'm unfamiliar with putting code in Excel. Any help
is appreciated.

Pixie,

This is exactly what I am looking for to "begin" a spreadsheet for vacation
calculations. Is there any way you can share the formulas with me (a
beginner as far as this goes). Is the "if" formula the only one needed? How
do you pull info from Column D to Column E, etc. Does this question make
sense?

"Pixie78" wrote:

I am trying to automatically calculate vacation hours. I have the employees
hire date (column D), their years of service (Column E) which is calculated
by subtracting todays date by their hire date and put into numbers, and then
I have their vacation time allotted (Column F). Here's how the hours go:

1 year but less than 3 gets 80 hours
3 years but less than 15 gets 120 hours
15 years but less than 25 gets 160 hours
25 years and over gets 200

I tried making criteria under tools, name, define and making a string like:
If(AND(E61,E6<3),80,IF(AND(E63,E6<15),120)) and it seemed to be working
for a couple cells but on others it rendered False.
Also, this will be for different spreadsheets so on some I will have only 10
employees but on others I will have many more. If there a way to type in
criteria that would copy down the cells of employees so that whatever their
Years of service equals their vacation hours would equal this? I've built
databases in Access but I'm unfamiliar with putting code in Excel. Any help
is appreciated.