M
maxwellkitty
Hello,
I am in need of assistance to calculate overtime for over 100 employee
averaging in 3000 hours per month. A very tedious task because we pa
out OT from the previous month and I would greatly appreciate ideas o
figuring OT hours accurately and quickly. Unfortunatly, our tim
sheets are linked to our billing system, our accountants input al
their time and then use an overtime code to back out any hours above
hours worked and to balance out to 8 hours they will use a code fo
vacation, sick, or PTO to balance their time to 8 hours. At the end o
the month, I export 3 reports, one for the hours coded to OT, the
Vacation,Sick, and PTO. When I use the export for the "Just the "O
and use a Pivot Table to format the hours ..Below is a screen shot.
And I copy it over to a new sheet to insert rows to analyize each wee
for OT anything UNDER 4hrs Mon-Fri and another row for Doubletim
anything OVER 4hrs Mon-Fri and then I export the vacation, time of
code and again format to another pivot table. From there I go back t
OT pivot and lessen OT by time off. Example: Ee worked a total of 5
hours a week and coded 10 hrs to OT but padded 5 hrs of thier time t
vacation, for OT is based on actuall hours worked I need to pay dail
OT and hours over 40 at OT rate (also any hours over 8 at doubletim
for hours worked on the 7th consecutive day.) However, I will deduct
hours and pay this at the regular rate.
Sum of Hours Dept ID
Date CWH SF
1/1/2004
1/2/2004
1/3/2004
1/4/2004
1/5/2004 (0.25)
1/6/2004
1/7/2004 (1.00) (2.50)
1/8/2004 (0.50)
1/9/2004 (0.75) (0.50)
1/10/2004
My newest idea:
I thought I would take my export and add 8 hours to every weekda
before formating to a pivot table and inserting rows after each wee
period. One to sum total hours, one to sum hours over 8 but under 1
and any hours after summed over 40 hours. And of course, what would b
the best way to mash the vacation export to lessen the hours worked o
the day vacation was used rather than manually going back and enterin
the vacation used as a positive number to lessen the OT. I apologiz
for such a lengthy message but I am at my wits end and am unsure of ho
to present my delimma(also its way past my bedtime) Please let me kno
if you have any ideas to help make this task easier, quicker an
accurate..the present way of doing this leaves to many mistakes t
easily be made by human error.
Appreciate the help greatly and if possible could you also email you
tips to me directly at: (e-mail address removed)
Thanks so much,
Shana;
I am in need of assistance to calculate overtime for over 100 employee
averaging in 3000 hours per month. A very tedious task because we pa
out OT from the previous month and I would greatly appreciate ideas o
figuring OT hours accurately and quickly. Unfortunatly, our tim
sheets are linked to our billing system, our accountants input al
their time and then use an overtime code to back out any hours above
hours worked and to balance out to 8 hours they will use a code fo
vacation, sick, or PTO to balance their time to 8 hours. At the end o
the month, I export 3 reports, one for the hours coded to OT, the
Vacation,Sick, and PTO. When I use the export for the "Just the "O
and use a Pivot Table to format the hours ..Below is a screen shot.
And I copy it over to a new sheet to insert rows to analyize each wee
for OT anything UNDER 4hrs Mon-Fri and another row for Doubletim
anything OVER 4hrs Mon-Fri and then I export the vacation, time of
code and again format to another pivot table. From there I go back t
OT pivot and lessen OT by time off. Example: Ee worked a total of 5
hours a week and coded 10 hrs to OT but padded 5 hrs of thier time t
vacation, for OT is based on actuall hours worked I need to pay dail
OT and hours over 40 at OT rate (also any hours over 8 at doubletim
for hours worked on the 7th consecutive day.) However, I will deduct
hours and pay this at the regular rate.
Sum of Hours Dept ID
Date CWH SF
1/1/2004
1/2/2004
1/3/2004
1/4/2004
1/5/2004 (0.25)
1/6/2004
1/7/2004 (1.00) (2.50)
1/8/2004 (0.50)
1/9/2004 (0.75) (0.50)
1/10/2004
My newest idea:
I thought I would take my export and add 8 hours to every weekda
before formating to a pivot table and inserting rows after each wee
period. One to sum total hours, one to sum hours over 8 but under 1
and any hours after summed over 40 hours. And of course, what would b
the best way to mash the vacation export to lessen the hours worked o
the day vacation was used rather than manually going back and enterin
the vacation used as a positive number to lessen the OT. I apologiz
for such a lengthy message but I am at my wits end and am unsure of ho
to present my delimma(also its way past my bedtime) Please let me kno
if you have any ideas to help make this task easier, quicker an
accurate..the present way of doing this leaves to many mistakes t
easily be made by human error.
Appreciate the help greatly and if possible could you also email you
tips to me directly at: (e-mail address removed)
Thanks so much,
Shana;