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

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

On Dec 27, 7:26*am, jd_dps wrote:
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)MON TH(C2),DAY(D2)<DAY(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

Thank you. the hours calculated perfectly.


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