Function to figure vacation accrual

[Liz]

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.

 

[cmoore]

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 woul
the 5 years hurdle be met

 

[Ozzie]

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

 

[Liz]

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

 

[cmoore]

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 (calende
years).

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


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

=IF(NOW()-A5<14,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 help

 

[Glen]

Have you by any chance figured out that array formula?
I'm trying to do the same thing with HR Dept; namely, after 3 months of employment, employee begins to accrue 5/9 of a day per month until their anniversary date, then they accrue 5/12 of a day per month unitl their 3rd anniversary date, at which time they accrue 5/6 of a day per month until 10 years of service.
Is their a template that you know of which will do this by entering in their date of hire and the current date?

----- Ozzie wrote: -----

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