I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John

Try

=INT(A2/365)*5

Where A2 is days worked

mike

"John5835" wrote:

I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John

THANKS,

How would I do it the vacations are a little different, i.e.

after 1 year of work they receive 5 days vacations
for the 2nd, 3rd, and 4th year, vacation days equal 10 days
for the 5th, 6th, 7th, 8th, 9th year vacation days equal 15 days.
and then 10 years on, total vacation days equal 20.

1yr=5 days
2, 3, 4 yr= 10 days
5,6,7,8,9 yr = 15 days
10 and on = 20 days
--
thanks,
John


"Mike H" wrote:

Try

=INT(A2/365)*5

Where A2 is days worked

mike

"John5835" wrote:

I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John

Becaise your now working in years I'd do it this way

=CHOOSE(MIN(A1,10),5,10,10,10,15,15,15,15,15,20)

Mike

"John5835" wrote:

THANKS,

How would I do it the vacations are a little different, i.e.

after 1 year of work they receive 5 days vacations
for the 2nd, 3rd, and 4th year, vacation days equal 10 days
for the 5th, 6th, 7th, 8th, 9th year vacation days equal 15 days.
and then 10 years on, total vacation days equal 20.

1yr=5 days
2, 3, 4 yr= 10 days
5,6,7,8,9 yr = 15 days
10 and on = 20 days
--
thanks,
John


"Mike H" wrote:

Try

=INT(A2/365)*5

Where A2 is days worked

mike

"John5835" wrote:

I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John

hi
you can nest the ifs;
if(A11095,120,if(A1730,80,40))
you can nest 7 ifs in xl versions 2003 or lower. in 2007, i am told it's
more, 64(i think) but don't quote me yet.

Regards
FSt1

"John5835" wrote:

I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John

=A1/9.125


"John5835" wrote:

I'm trying to create a formula to calcuate vacations days, onee cell has # of
days worked and I want another cell to calculate # of vacations by # of days
works, i.e for every 365 days worked, each employee receives 5 days (40
hours) of vacation. 730 days they have 10 days (80 hours), 1095 days worked
they have 15 days (120 hours), etc., etc.

=IF(A1=365,40,0) this works for the first year but doesn't account for
730, 1095, etc.

any advice would be greatly appreciated
--
thanks,
John