HELP!!! I need Gnatt chart/table help I think

**Kory95670** · 06-03-2013, 01:13 PM

Okay. Here is a description of my problem.

I work in an office with about 80 staff. My management has asked me to create a database and informational comparison spreadsheet to compare staff schedules including starting times, breaks, lunch times, and leaving times. It is a call center that does have tracking software for calls that occure between 8:00am and 5:00 pm. My management wants me to develop a dashboard and system of charting everyones scheduled shift so there can be a comparison and a full graphic bar chart. I am running into issues when I try to format the graph. I need to overlay and stack the times. When I try it, they never line up correctly. Can anyone help me design a way to show a chart that overlays start time>1st break>lunch>second break> and end time?

Thanks.
Kory-

**Kory95670** · 06-03-2013, 01:15 PM

here is the raw file im working with:

**GeneralDisarray** · 06-03-2013, 01:33 PM

what exactly do you want to see presented?

**Kory95670** · 06-03-2013, 04:12 PM

I need a stacked chard showing start time to 1st break then after 1st break is over time until Lunch, etc. colored lines to show when people are off the phones for break and lunches. and time available for phones.

**GeneralDisarray** · 06-04-2013, 11:21 AM

This seemed interesting, but I don't know how to do it exactly with a normal chart

But it is a neat problem you can handle with conditional formatting. Take a look at the attachment. Basically, you could list the days times in 15 minute blocks (as i have shown in the example) and then use a formula to determine what the employees status is for that block: off, on, break, lunch.

There are 8 cases Off, On before break1, at break 1, On after break 1 before lunch ...

you get the idea. It's not as bad as it might sound, because the cuttoffs are all given in your table. Basically, the problem involves:

1) author a long "Choose" function to determine the status for each 15 minute block: "off", "on", "break1", "lunch", or "break2"

2) use conditional formatting to color the cell based on the status. I picked: off = grey, on = blue, break or lunch = orange.

THe colors will then "stack out" for the selected employee as you seem to want, but it's not technically done with a chart. IF this makes sense and you need help implementing it, please reply.

----

Edit: you caught me on a slow morning and this was an interesting problem

Check out the the second attachment. If you alter your top table a bit, you can make use of VLOOKUP (which you seemed fond of) in place of the long CHOOSE funciton. I thought of this as I was posting up the first book. Have a look :D

=IFERROR(VLOOKUP(B21,$D$12:$G$19,4,TRUE),"OFF") - You will need to add 3 rows to the table and then one column (see example) to make this work.
=IFERROR(VLOOKUP(B21,$D$12:$G$19,4,TRUE),"OFF")