Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tresscott
 
Posts: n/a
Default How to chart start and stop times

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
  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

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.


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

tresscott wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
what should i use darryll Excel Worksheet Functions 3 March 6th 05 06:26 AM
How can I get elapsed time between AM start/PM stop khacmac Excel Worksheet Functions 1 February 2nd 05 12:17 AM
Auto x range start value, xy charts [email protected] Charts and Charting in Excel 5 January 25th 05 12:49 AM
How do I stop the Reviewing toolbar from showing up in Excel? RickKel Excel Discussion (Misc queries) 2 November 26th 04 06:37 PM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"