ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   Timeline Chart? (https://www.excelbanter.com/charts-charting-excel/17857-re-timeline-chart.html)

ckrogers

Timeline Chart?
 
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" wrote:

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/
_______

Jamie wrote:

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" wrote in message
...

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 wrote:


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" wrote in message
...


Jamie -

This is called a Gantt chart. I have an example he

http://pubs.logicalexpressions.com/P...cle.asp?ID=343

and a list of links to other examples he

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

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

Jamie wrote:



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

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/
_______

ckrogers wrote:

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?


ckrogers

:) 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" wrote:

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/
_______

ckrogers wrote:

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

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/
_______

ckrogers wrote:

:) 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" wrote:


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/
_______

ckrogers wrote:


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?




All times are GMT +1. The time now is 11:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com