I have to track the number of hours my employees work each month. Based on this data, employees may or may not qualify for a program. In addition, the reporting period is different for employees depending on when they are hired.
Currently, I am maintaining seperate workbooks based upon hire dates. Within each sheet, I have a table that lists each employee and the hours they worked each payperiod with a seperate sheet for each month.
I wondering if there is a way to maybe use a pivot table(?) to maintain all the information in one workbook and see the relevant information.
I do have some calculated fields in my tables right now, as I need to maintain a running total for a 12 month period, as well as calculate the average number of hours based on a 12 month period. (So Jane worked 110 hours in May, 90 hours in June, 150 hours in July for a running total of 350 hours, giving her a 12 month average of 29.17 - the average will change as months/hours are added). If Jane reaches an average of 130 or more in the 12 month period, she qualifies for the program.
Does this sound like something I can simplfy or do I need to keep at it the way I'm doing it. Keep in mind, I'm just starting to learn about Pivot Tables...
I've attached an example of one of my workbooks.
Any advice on simplfying this project would be appreciated!
Bookmarks