creating beginning vacation use and tracking it

[Guest]

How do I create a worksheet to use employee's hire date and to track vacation
availability based on the hire date?

 

[Guest]

Vacation allowance is usually based upon years of service or years of service
on a certain date. Tell us the rules and we will give you the formulas.

 

[Bob I]

At a minimum you would need an expression that had the accrual rate, an
entry for the hire date and today's date. And then a column that summed
the past use so you could subtract that from the total accrued.

 

[Guest]

vacation days based on 1 year of service from hire date.

1 to 4 years of service = 40 hours
5-10 years of service = 80 hours

Does this help?

 

[David Biddulph]

=IF(DATEDIF(A1,TODAY(),"y")<1,0,IF(DATEDIF(A1,TODAY(),"y")<=4,40,IF(DATEDIF(A1,TODAY(),"y")<=10,80,"undefined")))

 

[Guest]

If the date of hire is in A1 then:

=CHOOSE(DATEDIF(A1,TODAY(),"y")+1,0,40,40,40,40,80,80,80,80,80,80)

This gives 0 if service is less than 1 year.

 

[Guest]

The next step is "how does it accrue?". Gary''s Student has provided a
formula that kind of just drops the total hours into place on their
anniversary, but it's usually not that simple?
For example, company I work with lets you accrue up to 80, 120, 160 hours
per year based on your service with them, but you don't get a whole year's
worth at once. The total possible to accrue is spread out and given to us
based on the hours we've actually worked so far, so we accrue a few hours
each pay period, enough so that at the end of the year we will have accrued
the max available for our length of service. Additional rules involved is
that only actual hours work count towards accrual: so hours for holidays and
vacation don't count toward building more vacation time.

You can come up with a formula for figuring out how much vacation to give
per hour worked relatively easily. Start with maximum hours for a work-year.
This will depend on whether or not you observe various holidays. Lets say
you observe the 10 federal holidays that most federal agencies give as paid
off-time.
52*40 = 2080 hours - (8hrs * 10 fed holidays) = 2000 hours.
Subtract the # of hours they could earn during that year:
2000-40 = 1960 work hours to accrue 40 hours of vacation, or .0204 hours of
vacation given for each hour worked. Which works out to .8163 hours of
vacation for each 40 hour week worked. (to check: .8163 * 49 = 39.9987 hours
of vacation time built up).

 

[Guest]

You need to know when he started, how many days he gets per year, and how
many he has already taken. Else, on a year to year basis, when his leave
cycle starts, how many days per year he gets, how many days were brought
forward from the previous year, and how many was taken this year.

Lets say you have start date in B2, leave allocation per annum in C2, leave
taken in D2, then for the first scenario, in E2 enter
=ROUND((NOW()-B2)*(C2/365)-D2,0)

For the 2nd scenario, you could have leave cycle start date in B2, leave
allocation per annum in C2, leave brought forward in D2, leave taken this
year in E2, and then in F2 enter =ROUND((NOW()-B3)*(C3/365)+D3-E3,1)

If you have a situation where leave can be accumulated indefinetely, then
the first scenario should work best. However, if you have a situation where
leave may only be carried over to the next year, but is forfeited if not
taken in the 2nd year, then scenario 2 will work better.