Forum Discussion
Employee Punch Report
mathetes: Hello and thanks for your response! I'm very new to this so I apologize if my question and terminology didn't make sense. Here is my raw data: https://kansas-my.sharepoint.com/:x:/g/personal/j566l030_home_ku_edu/EX-VJQi4qF5Chbqw2xrWOosBNU_lgFqMC4ubYP_teuBOnQ?e=4mbJ8u
I am needing to calculate average number of shifts worked per week and average length of the shifts. I'd also like to have the average number of hours worked per week. These calculations would all be by "Department". I will be reporting on this information weekly but would need the ability to go back and analyze previous weeks, months, years. Hopefully this help. Thanks so much!
The Pivot Table is a tool in Excel that (I think) will do what you want to do--no formulas needed. I've attached a couple quick examples to show you reports that it can generate. I did take the liberty of adding two columns to your data, helper columns that translate the date filed into (a) Year, and (b) Week number of that year (i.e., 1 - 52 or 53), in sequence throughout the year.
The examples I created are not exhaustive. I didn't, for example, do average length of shift, but that would be quite easy. My purpose at this stage is just to show you what most people consider--once they've learned it--a very quick and easy way to summarize the kind of data you have. That "easy way" is the Pivot Table.
I let the Pivot Table routine do the summing (of hours per week per department) and that looks like this for 2023. You can change the year selected, there in the top row, when you open the attached file (the report for 2022 had more weeks, so was really too large to be practical here as a screen image).
For the number of shifts, I made an assumption, and that assumption was that each ID counted as one shift for that day. So what it counted, in fact, was number of IDs per department per week. It looks like this.
So my question for you is whether this kind of summary is what you've been asked to produce. If so, the Pivot Table is the tool for you. To learn it to be able to do it, let me provide links to two sources.
- First, ExcelJet, a web page I've found that gives clear instructions on all aspects of Excel.
- Second, YouTube has videos on lots of topics. Here's a link to some YouTubes videos on how to create and use Pivot Tables.