Calculating total hrs, average per person, and then...amt above or below average used.
Posted by Bill on July 18, 2001 1:38 PM
I am not sure how to create an overtime tracking system with excel. I need to make a list of the employees, keep a running total of the number of overtime hrs each uses during a quarter, and be able at any day (or every day) produce a list or report showing the employees..the average ot used to date, and the relation + or - to the average each employee has so the hours can be given out fairly by the end of the quarter.
I have limited knowlege of how to make a pivot table but I am guessing that it would be the way to do it.
If anyone could help I would greatly appreciate it!
Posted by Eric on July 19, 2001 7:25 AM
Just an idea
Since you know the beginning and end of the quarter, for this example i'll assume 91 days, in sheet 1 (Call it "OT"), you could put the dates for that quarter in (b1:cm1). Also assume you have 199 employees, in (a2:a200) put the names of the employees. Then fill in the overtime hours for each employee up to the current date. On a second spreadsheet, call it "report", in (a1) type the formula: ="average O/T through "&today()&" is "&average(OT!b2:cm200). copy column (b) from sheet "OT" to sheet "report", label(c1) "#hrs away from average" and in (c2) type the formula: =average(OT!b2:cm2)-average(OT!b$2:cm$200), and copy and paste the formula through (c200). The results in the (c) column should be positive for employees who have more than the average overtime, and negative for employees with less. you can custom format the (c) column to highlight the differences. The average function doesn't include blank cells, so even though the formula encompasses the entire quarter, only dates for which overtime has been entered will be included in its calculation.
This assumes that you have just the overtime data for the employees, and that the overtime is in "general" format (e.g., one and a half hours of overtime shows as "1.5", not "1:30"). There are a few more steps if you have total hours worked instead of just overtime, and if employees are allowed "flex time", or other variables.
Hope this helps.
