Function to figure vacation accrual

Help!!! I currently have a vacation spreadsheet in which
I need to create a column to show vacation accrual per
month. Based on their length of service with the company,
employees receive a set amount of hours of vacation each
month (ie, 5 years = 3 weeks or 10 hrs per month.) How
would I create a formula to show this? I do have a column
with the employee's actual start date...however, we are on
a calendar year and not an anniversary year.

A2 contains the start date so B2 =DAYS360(A2,TODAY())/365
this gives you the length of service years. Then just do
an if formula with your parameters.. if years less then
5, then .833 days per month, if greater then 5 and less
then 10, then 1.0 day per month, otherwise 1.6777 day per
month.

There is an array formua that I believe could be used, but
I've not mastered it yet... soone iwll probably provide it.

HTH...


-----Original Message-----
Help!!! I currently have a vacation spreadsheet in which
I need to create a column to show vacation accrual per
month. Based on their length of service with the
company,
employees receive a set amount of hours of vacation each
month (ie, 5 years = 3 weeks or 10 hrs per month.) How
would I create a formula to show this? I do have a
column
with the employee's actual start date...however, we are
on
a calendar year and not an anniversary year.
.

If someone started in December of 1998, then as of January
2003 they will already be eligible for 3 weeks of
vacation. It doesn't matter which month you are hired.
It's what year you were hired in. So 2003-1998 = 5
years. So really as of 1/1/2003 they've only been with
the company a little over 4 years and not the complete 5
years. Upon hire you have to work a complete month in
order to accrue vacation. If you were hired 4/15/03, you
would start accruing vacation as of 5/1/03. Since those
employed less than 5 years only accrue 2 weeks of vacation
a year they would accrue 6.67 hrs a month (80 hrs divided
by 12 months). Talk about a lot of calculations in 1
spreadsheet!!!

-----Original Message-----

what you mean by "calender year vs. anniversary years"?

Give an example of the accrual based on hiring date.

If hired in April 2003, how many hours would the person
get now.

If hired in Dec of 1999. Would it be 10 hrs /month yet.
When would
the 5 years hurdle be met?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Assumptions for this equation

Cell A5 contains the start date,
No vacation until 14 days after start date,
6.66 hrs/month of vacation for service between 2 weeks and 5 years,
10 hrs/month of vacation for service of 5 years or more (calender
years).

=IF(NOW()-A514,0,IF((YEAR(NOW())-YEAR(A5)5),80/12,120/12))


In case there is an additional tier here is the equation assuming at 10
years you get 13.33 hrs/month.

=IF(NOW()-A514,0,IF((YEAR(NOW())-YEAR(A5)5),80/12,IF((YEAR(NOW())-YEAR(A5)10),120/12,160/12)))

If there are additional tiers, you can follow the logic.

Hope this helps


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/