Excel Time Management Chart

[ChrisWolf]

Hi,

I have been asked to create a chart that shows a persons work flow over a day over a set number of hours.
What I need to do is create a chart that shows hours from 7am until 7pm across the bottom line then shows different colours and the same row depending on the activity.

I have the data collected however I am unable to create the chart to show the activity on the same row.

I can create a pivot table to filter the data for the chart but I am unable to setup a field that filters the time correctly as I need the time to be set to 7am to 7pm.
Chart Example1.jpg

This is how I want the rows to look listed down the left hand side will be the agents and the different colours represent the different States the agents enter during the day at the time of day they enter the state.

Any help with this would be greatly appreciated.

Regards,
Chris.

[Pete_UK]

A small picture is not much use to us - it would be better if you attach a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon for attachments, as it doesn't work on this forum.

Hope this helps.

Pete

[ChrisWolf]

Hi,

Apologies I have attached a sample of the data sheet that I use.
The image is what I trying to accomplish.

How I was to represent the data is to have the Agent Name in the rows and the State showing across a time bar that shows when the states started between 7am and 7pm.

Regards,
Chris.

[Pete_UK]

The data in the file you attached just covers one day. Is this representative of your real data, or are you likely to have a mix of dates in that sheet? If many dates, then would you envisage having the facility in the "picture" sheet to be able to select a particular day?

If we have the coloured bars going across the "picture" sheet, would you envisage an increment of, say, 15 minutes for each column, or do you see this more as a horizontal stacked bar chart?

Pete

[ChrisWolf]

Hi,

I couldn't send the entire sheet because of the amount of data. So far I have this year from January so you can only imagine how many rows there are!
I was imagining a horizontal Bar Graph to show the output but was unable to create the axis for the set times the data needs to show for.

I need it to show times between 7am and 7pm and have a different colour to show the States.

Regards,
Chris.

[Pete_UK]

Yes, but presumably you want to do that for a single day, rather than try to generate a consolidated day (i.e. "averaged") for the period. I'm not sure how such "averaging" would be accomplished, as at a particular time one day the agent might be "released" and the next day at the same time s/he might be "In call", and then on the next day at the same time the state might be "Ringing".

Pete

[ChrisWolf]

I was hoping to be able to use a pivot chart so that I can select the agent and day to filter the information. Unfortunately I am unable to create the link to the time frame from the Start Time of the State.
Not sure it would be possible to show more than 1 day because of the difference in the start time of the states.

Unless the pivot chart is setup to show a certain amount of days in rows for a single Agent and you just choose the agents name from the list. Would that be possible ?

[Pete_UK]

In your picture you show 5 different colours in the bands - are these meant to represent the 4 different states in column J (plus maybe break times), or is there some other state column that I should be looking at ?

I see that for the first Agent in your file, there are some overlapping times. For example, then EndTime for row 4 is 08:51:59, which is then the StartTime for rows 5 and 6. Row 7 uses the EndTime from row 5 as its StartTime, and the EndTime of row 6 as its EndTime, so presumably these three rows all relate to the same state. I'm confused, so perhaps you could elaborate on what is happening here.

Pete

[ChrisWolf]

Hi,
Apologies for the delay in replying I have been extremely busy.
What I need to do is have a graphical representation of an Agents time management over a given day so I can filter the chart by date and agent name but the main body of the chart needs to shop what states the agent has been in during the hours of 7am and 7pm.

The state will be a mixture of column J as the main state then column L for the release code in the event the state is Released.
Regards,
Chris.

[ChrisWolf]

Ideally as well I would like to have a set colour for each state in column J.
Regards,
Chris.