Oooooh, my head's going to explode because this is so new to me. Ouch!
See attachment for more visual representation
The information I have:
1. a string of dates in no set interval (ie. May 5, May 8, June 13)
2. each day is divided into half-hour blocks (for a nightshift so ie. 18:00, 18:30, 19:00.... to 06:00)
3. there are up to 6 agents working during these blocks of time.
Unfortunately, my collegue created a Word table for each day with the x axis part being the half-hour blocks and the y axis being a row per agent. She then just colour-coded the agent's row in the appropriate cells to visually indicate which half-hour blocks were worked (see attachment).
Ideally, I'd love to figure out how to import all the tables into Excel in a useable format. But I haven't figured out how to do it for the 43 pages-worth of tables (about 5 tables to a page).
My main problem is, however, I'm not sure how I should create an Excel spreadsheet that I can use to then manipulate the data into a chart which would show the cluster of 6 agent's bars according to the date along the x axis and the half-hour blocks as the y axis. The goal of the chart is to visually see over a period of dates what time each of the 6 agents worked overtime on that date (most of the time only 2 of the 6 agents do overtime). We're hoping to visually see patterns of overlapping and show it to people without overwhelming them with figures and tables.
For the life of me, I can't figure out how to set up an excel spreadsheet to capture the information in a useable format for the pivot table or chart function to understand. I tried running down the half-hour blocks in the 'A' column, running the Agent 1, Agent 2, etc. along the 'B', 'C', etc. rows and colour-coding the cells but I'm not sure that a chart or pivot function will understand coloured cells and I can't figure out how to define what I want as the x axis and the y axis... .
I'm not even sure of what vocabulary I should be using to do a google/excelforum search.
Help? Please???
Bookmarks