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.

 

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