vacation accrual formula

[jd_dps]

I am using Excel 2003. I am trying to find a formula to calculate the number
of vacation days each employee will have on 1/1/09. I calculate the vacation
time using hire date; however starting 1/1/09 we are switiching to a calendar
year, but will still have to use the hire date for accrual.
Policy: after 1 year of service = 1 week of vacation =0.416667 per mo.
accrued : after 2 years of service = 2 weeks of vacation = 0.83333 per mo.
accrued: after 8 years of service= 3 weeks of vacation = 1.25 per mo. accrued.
What I have so far in my spreadsheet:
Column A: Name
Column B: Address
Column C: Hire Date
Column D: Todays Date
Column E: Time Employed (using formula this is broken down to Years, Months,
Days)

Example:
A:Name B:Address C:Hire Date D:Today E:Time
Employed
A1:Jon Smith B1:main st C1:11/7/2006 D1:12/26/2008 E1:2yrs,1mo,19days

I would like to be able to put Column F: Number of vacation days

 

[Fred Smith]

You need to clarify your requirements.

1. Using your example, is Jon Smith entitled to 2 weeks of annual vacation
starting Nov 7, 2008 or Jan 1, 2009?
2. Do you want to calculate the monthly accrual, or the number of
outstanding vacation days?
3. With the information you have, you can determine how much to accrue each
month, but you won't know how many vacation days the employee is entitled to
unless you also keep track of how many they've taken.

Regards,
Fred.

 

[jd_dps]

The anniversary date is still used to aquire additional vacation s
basically on Nov. 7, 2008 this person picked up his additional days
however, starting 1/1/09 this person will have 10 days of vacation whic
will start on 1/1/09 through 12/31/09 and keep 10 days of vacation unti
Nov. 7 2016 when he will pick up the additional 5 days and from there i
will renew every year on 1/1/? of each year. i would like to calculat
the amount of accrual monthly and also keep up with days used sinc
there are not roll over days from year to year.

 

[Mariela]

Hi - I am trying to figure out the same but haven't been able to - how did
you come up with the results in your E column.