SumIf Formula help to figure CA Overtime Laws

[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;

 

[BrianB]

The main principle of the forum is to keep all correspondence within th
group. Rarely do we deal direct. This is better for everyone concerne
because none of us knows "all the answers". We get email notificatio
when something is added to a thread. It also avoids duplication o
effort. .

RAW DATA
First off you need to be thinking in terms of your raw data ideall
consisting of a simple table with headings in row 1 and data below. I
it is in this format one pivot table will summarise for each employee
It looks like you have to combine data from several sources. Is thi
really necessary, or can an overall report be produced ?

TABLE HEADINGS
You need to have Date/Employee/Normal Hours/Overtime Hours/Doubl
Overtime Hours/Vacation/Sick/PTO etc. Perhaps another for Norma
Overtime hours if a calculation is necessary. If the data comes fro
several sources you just add it to the bottom of the table, ensurin
that the numbers go into the correct columns, the Employee being th
common link. Additional columns can be used to contain an
calculations required. such as Double Overtime.

If you need to make reports, do not worry too much at this stage. Ther
are usually 3 stages to analysis :-
Raw Data ... to Pivot Table (s) ... to Report.

Hope this helps