How to chart start and stop times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a graph of start and stop times for a process and having some
trouble with the data.

I am using a stacked bar with 3-d visual effect and have days on the y axis
and times on the z axis (i would call it the x, but excel seems to use z)

I have figured out how to get the times at the bottom to be even.
Then I figured out how to get 23:30 to be before 1:30 (I changed the latter
to 25:30)

but now the line ends before the axis label in the amount that the process
starts before midnight.

an example
if my process starts at 23:00 and ends at 5:00 (29:00)
the graph will look like this

4/1/05 | XXXXXXXXXXXXXXXXXX
----------------------------------------
23:00 4:00

any hints other than manipulating the data further than I already have to.

for bonus points, you could help with the 23:00 is earlier than 2:00 problem.

Thanks
 
If you use true date-time values for start and stop, like

April 10, 2001 17:50

instead of just 17:50, you don't have to fudge by changing 1:30 to
25:30. Excel stores dates as whole numbers (days since 1 Jan 1900, give
or take an erroneous leap year), and times as fractions of a day (6 am =
0.25, noon = 0.5, 6 pm = 0.75); date times are a composite of a whole
number and a fractional number. The date and time value above is stored
internally as

36991.7430555556

If you want bars showing start and stop time, you might use the
following structure in the worksheet (assume it's in A1:E4):

Start End Chart Start Chart Duration
4/1/2005 4/1/2005 18:00 4/2/2005 1:30 1/0/1900 18:00 1/0/1900 7:30
4/2/2005 4/2/2005 17:00 4/3/2005 2:00 1/0/1900 17:00 1/0/1900 9:00
4/3/2005 4/3/2005 17:30 4/4/2005 2:30 1/0/1900 17:30 1/0/1900 9:00

Columns B and C have the true date-time, column D has the difference
between the true start time and the date (D2 has the formula =B2-A2) and
column E has the duration (E2 has the formula =C2-B2). Ignore the
1/0/1900 in columns D and E (format the cells as time only, not date and
time), since 1/0/1900 is the date representation of zero.

Make your floating bar chart using columns A, D, and E.
any hints other than manipulating the data further than I already have
to. for bonus points, you could help with the 23:00 is earlier than
2:00 problem.
Click to expand...

The above solves the 23:00 < 2:00 problem, and using real date-time
values to start with is not so much a manipulation as a correction. Too
bad it's not clear how Excel treats date-time values until you've
guessed wrong. But going forward, it's going to be a robust approach.

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