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" wrote:
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
with @tiscali.co.uk
"AFroines" wrote in message
...
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!
--
Afroines