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)<M ONTH(C2),DAY(D2)
=DAY(C2)),12,IF(AND(MONTH(D2)MONTH(C2),DAY(D2)<D AY(C2)),-1)))&"
months,
"&D2-DATE(YEAR(D2),MONTH(D2)-IF(DAY(D2)<DAY(C2),1,0),DAY(C2))&" days"


--
jd_dps
------------------------------------------------------------------------
jd_dps's Profile: http://www.thecodecage.com/forumz/member.php?userid=71
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44345