Need Formula for Calculating Accrued Vacation Time

[jd_dps]

Using Excel 2003. i have hire date and today(). i have a formula that
calculates time employed in years, months, days. i need a formula that
can help me figure out the number to vacation days accrued starting on
1/1/09. Policy: after 1 year of service = 5 days or 40 hours. after 2
years of service = 10 days or 80 hours. after 8 years of service = 15
days or 120 hours. Accrual is based on hire date; however we are
starting 1/1/09 on a yearly schedule for vacation. Therefore the formula
is not that simple. i need to know how many days each person will have
according to their start date on 1/1/09. days are not rolled. here is
what i have:
Worksheet:
Header Row
A1: Employee
B1: Address
C1: Hire Date
D1: Today()
E1: Time in Service
=YEAR(D2)-YEAR(C2)-IF(OR(MONTH(D2)<MONTH(C2),AND(MONTH(D2)=MONTH(C2),
DAY(D2)<DAY(C2))),1,0)&" years, "&MONTH(D2)-MONTH(C2)+IF(AND(MONTH(D2)
<=MONTH(C2),DAY(D2)<DAY(C2)),11,IF(AND(MONTH(D2)<MONTH(C2),DAY(D2)

=DAY(C2)),12,IF(AND(MONTH(D2)>MONTH(C2),DAY(D2)<DAY(C2)),-1)))&"

months,
"&D2-DATE(YEAR(D2),MONTH(D2)-IF(DAY(D2)<DAY(C2),1,0),DAY(C2))&" days"

 

[Mike H]

Hi,

There are several ways of working out holiday entitlement as of
1/1/09. here's one.

Build a table of service & holidays that looks something like this

1 40
2 80
8 120

In this case it's in columns O & P

the this formula will calculate for a start date in C1

=VLOOKUP(DATEDIF(C1,DATE(2009,1,1),"y"),$O$1:$P$3,2,TRUE)

Drag down for other employees
Note I've used DATEDIF which isn't documented in Excel. Google for it
to find out how it works.

Mike

 

[jd_dps]

Thank you. the hours calculated perfectly.