VACATION/SICK TIME ACCRUAL

[Guest]

Ok So I did not get an answer tha helpe dme in my previous question so I am
going to try it again. I am not sure if I explained what Iam looking for
correctly.

I am trying to make a formula to calculate an employee's vacation and sick
time accrual. Employees start accruing both after completing their first
sixty days. It is long so bare with me.......

VACATION
Each regular fulltime employee accrues 80 hours per year for the first five
years and 120 hours a year after five years of working service. Each part
itme employee accrues a percentage of the regular accrual based on what
percentage they work.EX-A part time employee who works 32 hours a week is
working 80% of a regular work scheduel and therefore recives 80% percent of
the vacation benefits.

SICK
Each regular fulltime employee accrues six days per year. Partime employees
accrue based on the percentage that they work as written above.

The accruals are calcualted according to the payroll schedule which is
semi-momthly so we have 24 payroll periods a year.

Both vacation and sick time accrual rollover at the end of the year.

I need an excel miracle for this!

 

[Sandy Mann]

A number of questions:

time accrual. Employees start accruing both after completing their first
sixty days.

So the employee starts to accrue vacation time at Start date +60 days. When
he/she gets to have 5 years service is the date the start date + 5 years,
Start date + 60 days + 5 years, January 1 of the year in which the employee
will attain 5 years service or January 1 of the year following when the
employee has 5 years service?

years and 120 hours a year after five years of working service. Each part
itme employee accrues a percentage of the regular accrual based on what
percentage they work

What, (in the spreadsheet) designates that the Part-time employees are
part-time?

The accruals are calcualted according to the payroll schedule which is
semi-momthly so we have 24 payroll periods a year.

How can we tell when a payroll period starts, ie Does it always start in the
1st Monday of the year? or when does it start?

Both vacation and sick time accrual rollover at the end of the year.

How do you "write off" vacation time when it is taken? ie if an employee
has 20 hours rolled over from last year and has accrued 24 hours this year,
takes 40 hours vacation do you want to simply enter the vacation in one box
and have it reduce the total accrued time? If so that will, I think require
a VBA solution. Doing it that way however means that there will be no
history of past vacations taken.

Do you want the rollover to continue year on year or do you want one sheet
per year?

A brief description of how the spreadsheet is laid pout would also be
helpful.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Some ideas to think about in this deal:
Most places don't permit accrual of leave while you are taking leave - you
accrue leave for hours worked. Where I work, we don't accrue while taking
'personal' or 'sick' time or when otherwise not-working such as Federal
holidays.

We look at it like this: There are 2080 work hours in a year (52*5*8). But
we're going to be off 80 of those hours (10 Fed Holidays), so now the time is
down to an even 2000 hours, but we also figure a person is going to take all
their vacation time during the year (not always true, but you cap the accrued
hours to permit roll over and otherwise account for unused vacation/sick time
[we just have personal time - use it for sick or vacation, our choice] and to
account for overtime worked. So for the OP's situation the people in <5 year
group get 80 hrs/year which reduces anticipated work hours to 1920. Now you
can calculate how much time is being accrued for each hour worked: 80/1920 =
..041667 hours accrued for each hour worked for the vacation side of things
(it's .06383 when they hit the 5 yr mark). Do the same kind of math for the
sick leave.

Then in the timekeeping system for a given pay period you have something
like this set up:
Previously Accrued Leave (some #of hours)
Used Leave (any taken this time period)
Accrued This Period (hrs worked * Accrual Factor)
Leave Balance (Prev - Used + Accrued)
You can set up in your time keeping to check the dates to determine which
accrual factor to use based on hire date. It gets a little more complicated
when their anniversary for change in accrual rate occurs in the middle of a
pay period, but that can be dealt with. If you make it a corporate policy
that new accrual rate at the 5 yr point starts with the first full pay period
after the 5 yr anniversary, things get simpler again - the only special case
then is the initial point when they hit the 60 day mark.

This all works pretty well when time off is permitted to be taken in hour
and/or fraction of hour increments.

All that's really left to do is determine the 'anniversary' date along with
the 60-day date to use as a flag to determine first whether or not to even
calculate the time accrued and second, when past the 60 day point, what
accrual rate to apply. Add another flag to further modify part-timers to 80%
and I think you're home free.

 

[Guest]

You guys might look at this - it doesn't try to do it all with one magic
formula, and I did not do anything with sick leave, but it is one possible
way to attack the problem.
http://www.jlathamsite.com/uploads/timesheet001.xls (Excel 97-2007 usable).
No macros, just formulas.

To deal with sick leave, you could calculate them in a similar fashion based
on hours worked and then divide by 8 and display integer result to show whole
days accrued/accumulated. Easier to do it all in hours: who likes to take a
full day of sick leave to make a 2-4 hour visit to the doctor's office anyhow?

I probably mis-handled the part time thing - set a flat rate of 80%, but
again, a little more work with a couple of formulas and you could figure it
out relatively easily. The tough part of that is figuring how many regular,
full time hours there are in the pay period since it varies depending on 1st
half of month or 2nd, and whether there were holidays involved. Divide
scheduled hours by total possible for a percentage in any given pay period.

I didn't use any named ranges - wasn't sure how familiar AFroines is with
them, but setting some up would make things easier to read and manage later.

 

[Sandy Mann]

Some ideas to think about in this deal:

<snip>

Just highlights how lucky I was when I was working - we just got 4 weeks
holiday each year and an extra week after 5 years regardless of all other
considerations. Made things much simpler <g>
--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Sometimes its simply amazing to see what lengths we go to to actually
complicate our lives.