Timeline Chart?

[Jamie]

I have certain employees that will clock in and out and various times
throughout the day. The total day runs from 8:00 am - 5:00 pm. I want to be
able to create a chart that will visually show me all the employees and the
clock in's/outs in a bar chart format. For example:

Tech name
Ryan ___________ _________ _____________
Tony ___ _____________ ___________ ________
Mark________________ _____________ _____

_______________________________________________
8am 9am 10 am 11am 12pm 1pm

That way I can look quickly and see when I only had one employee clocked in
and when all three were there.

Any ideas?

Jamie

 

[Jon Peltier]

Jamie -

This is called a Gantt chart. I have an example here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

and a list of links to other examples here:

http://peltiertech.com/Excel/Charts/GanttLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jamie]

Here's the problem
A Gantt chart trasks the timeline for individual tasks and shows the
relationships between tasks in a project. That being said one task will have
one start time and one finish time. In my question one person will clock in
and out at multiple times. It is like having a task that starts, stops,
starts, stops, etc. This all must be on one line otherwise the point of the
chart (comparison) would be ineffective. I want to be able to look at 9:00
am and tell who was clocked in and who wasn't in an bar chart format.

Jamie

 

[Jon Peltier]

Jamie -

So you make a chart with lots of stacked bars. Invisible for start, visible for morning,
invisible for break, visible for back, invisible for lunch, visible for in-cubical siesta,
invisible fro break, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jamie]

Thanks. Here is the problem I am now having
I set up the data sheet like this.

In Out In Out In Out In Out
Bob
Jane
Sara

For each person I enter the exact time they clocked in/out all for the same
day. I create the stacked bar and it seems to work, but on further
investigation I see that the time scale is off. It seems excel wants to
consider each in/out as a seperate day. The reason I say this is because the
time scale is 12:00:00 AM , then 12:00:00 PM repeated over and over. This
obviously throws the whole scale off. I attempted to change the scale (ie.
8:00:00 min and 18:00:00 max) but this REALLY throws the chart out of whack.
Any ideas?

Jamie

 

[Jon Peltier]

Jamie -

It's a stacked chart, so you need time differences, not absolute times. This means you need to
have two tables, one for the times (as you were making), and one that has elapsed time since the
previous time. Your first table would look like this:

In Out In Out In Out In Out
Bob 8:00 AM 10:15 AM 10:30 AM 12:30 PM 1:15 PM 3:00 PM 3:20 PM 4:25 PM

The second table would look like this:

Start Working Break Working Lunch Working Break Working
Bob 8:00 AM 2:15 0:15 2:00 0:45 1:45 0:20 1:05

Formulas would be used to subtract the appropriate times to get the elapsed times. Now your
stacked bars indicate the proper times.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

I'm using the steps found in

http://peltiertech.com/Excel/Charts/GanttLinks.html

to chart a timeline for quoting from Received From Customer->Submitted to
Estimating->Due Date->Done Date->Submitted to Customer. I even use the
diamonds at the end for the date finally submitted to the customer to close
out the quote. It works great! My one problem is when the quote is done
early (with a negative duration), the bars don't adjust so my end date is
that many days longer than it should be. Is there any way around that?

 

[Jon Peltier]

How could you finish the quote before starting it? Do you have a time
machine? Did you invent the flux capacitor?

I can't envision the difficulty, if all the data contain physically
meaningful durations.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

The problem is when the quote is DONE before it's actually due. For
example:

Received from Cust - Feb. 10
Submitted to Estimating - Feb. 11
Due Date - Feb. 15
Done Date - Feb. 13
Submitted Date - Feb. 13

Is there some way to show that it was done early? When I use Feb. 15 as the
"Due Date", even though the duration to the next step is -1, the "Done Date"
and my diamond for "Submitted Date" end up on Feb. 15. Am I making this
harder than I have to ... should I just be making the bar from "Submitted" to
"Due" end on Feb. 13 instead of 15?

 

[Jon Peltier]

Therefore the markers show actual and the bars show plan. The marker
doesn't have to stop at the end of the bar. Adjust the value (or
formula), so it stops on the 13th.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______