translate work schedule to chart counting hours

**wilsonta** · 03-11-2011, 02:29 AM

I really need help. I have a work schedule with a code for each person's job (position). I also have the hours listed that each person is working. The chart needs to categorize each employee by their code (position) and count their work hours this time counting them each hour they are there (see attached). The attachments are from last week's schedule so you should be able to see how the cashiers, stockers, produce, meat depts hours translate from the schedule to the graph.

I need help finding what kind of programming is needed and how to do this because right now it is ALL done manually. I am a novice but can learn quickly with the right tutorial or information or example. Please help me!

**MarvinP** · 03-11-2011, 03:43 AM

Hi wilsonta

I have a little different way of looking at the problem. If you could get your data into a list/table you could do wonders with it using Pivot Tables. Find sheet1 where I've rearranged your data to fit a list and did a pivot on it. See if this might help. You can do many types of pivot tables with data set up like this.

**wilsonta** · 03-11-2011, 04:19 AM

Originally Posted by MarvinP

Hi wilsonta

I have a little different way of looking at the problem. If you could get your data into a list/table you could do wonders with it using Pivot Tables. Find sheet1 where I've rearranged your data to fit a list and did a pivot on it. See if this might help. You can do many types of pivot tables with data set up like this.

That is a very interesting way to do it. I'm not familiar with Pivot tables but will look more into doing it that way. Another question is can the files be separate? The "graph" has to be sent into corporate while the schedule stays at the store. In fact, the person receiving the graph does not want to see the schedule (the entire reason he requested this type of graph be done). So, with pivot tables, do they work if the information is from one file to another and be emailed off without affecting the information in the file??

**MarvinP** · 03-11-2011, 10:37 AM

The easy way is to copy the Pivot Table information to the Window Clipboard and Paste Values Only to a second workbook. That way you can send it in a completely different workbook.

BTW - you don't have to do a sheet per week with Pivot Tables. If you keep the date with the time you can use Pivot Tabel Filters and easily filter out times you don't want included. You can also filter them in. Say you want to know hours per month. It is way too easy to do this with pivot tables. Keep learning

I woke up this morning worried about the Sum or Hours that I gave you. When displayed in Time 25 hours shows as 1 hour, because it is 1 day plus 1 hour. I was trying to remember the conversion. I'll have to review past posts to find the answer for that one.