+ Reply to Thread
Results 1 to 1 of 1

Creating a chart or bar graph to show when agents worked hours on a series of dates

  1. #1
    Registered User
    Join Date
    05-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Creating a chart or bar graph to show when agents worked hours on a series of dates

    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???
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1